Change the Coloring of Cells based on date

**DocH** · 10-19-2009, 01:39 PM

I need a macro that will search entire worksheet and if a date is listed, check that date and do the following:
1. If the date is greater then 60 days from todays date, do nothing.
2. If the date is less then 60 days from todays date, change the cell color to yellow.
3. If the date is less then 30 days from todays date, change the cell color to orange.
4. If the date is equal to or older then todays date, change the cell color to red.

Help!

**DocH** · 10-19-2009, 01:40 PM

So basically on the above:
#2 should be from 60-31 days from todays date.
#3 should be 30-1 day(s) from todays date.
#3 should be 1 day or older from todays date

**NBVC** · 10-19-2009, 02:00 PM

Would you consider a non-macro approach using Conditional Formatting?

**DocH** · 10-19-2009, 02:28 PM

Yes, whatever works as long as it checks this and updates the worksheet every time its open.

**NBVC** · 10-19-2009, 02:54 PM

Select the columns (ranges of interest) and go to Format|Conditional Formatting

Choose Formula Is, Enter: =And(A1<=Today()+60,A1>Today()+30)

click Format and choose from Pattern tab.

Click Ok

Click Add

Choose Formula Is: Enter: =And(A1<=Today()+1,A1<>"")

click Format and choose from Pattern tab.

Click Ok

Click Add

Choose Formula Is: Enter: =And(A1<=Today(),A1<Today()+30)

click Format and choose from Pattern tab.

Click Ok

Click Ok

Note: These formulas assume your selected range has cell A1 at top-left most corner. Adjust that in the formulas if necessary to suit

**DocH** · 10-19-2009, 06:04 PM

I see it only does 3 condidtions, but wouldn't I need 4:
1)Do Nothing
2)one for the 60 day check
3)one for the 30 day check
4)one for the today an older check

?