How to use traffic light conditional formatting icon set with dates.

I couldnt find this anywhere by searching it, so I figured it out and thought I would share the results.

I wanted to have a traffic light change color depending how close the current date was to a date in a particular cell in the spreadsheet. Green for more than 14 days away, yellow for less than 14 days away, and red for today or past due.

This would be useful for upcoming tasks/calander organizing etc.

I made it extremely easy to modify/edit for your own purposes, there are two parts. One part will update the entire spreadsheet, you could assing it to the button, the other part will make it so that any new changes to dates put into the spreadsheet will have it automatically update the corresponding traffic light.

The macro part you would add as a module:

Please Login or Register  to view this content.

and here is the party you would put on the sheet code:

Please Login or Register  to view this content.

You would need to format the column directly to the right of your dates (in this example, column 7) for conditional formatting with traffic light icon set, where green is >= 3, yellow is 2, and red is 1 and have it hide the data.

If you have any questions about this, let me know I'd be happy to explain further.

Ok so I have a problem with this code. It works 100% fine on Excel 2007. When I try to use it in Excel 2013, it seems when it is comparing the date, it is messing up and always is showing the wrong value, any ideas on why this would be?

Heres the fix when updating to 2013

Please Login or Register  to view this content.

Please Login or Register  to view this content.