Conditional Formatting

Copper Contributor

I'm trying to highlight different dates depending on how long ago they were on a sheet. For example Less than 30 days Green, Between 30 and 90 days Blue and greater than 90 Days red. I have tried a lot but either the dates won't turn color or the whole sheet turns to a color but the dates. I have been trying for the last couple of hours but I can't figure it out. Thanks. 



5 Replies


Select the range with the dates.

Set the fill color to green.

This will be the default.


On the home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Format only cells that contain'.
Leave the first drop down set to 'Cell Value'.
Select 'less than or equal to' from the second drop down.
In the box next to it, enter the formula


Click Format...
Activate the Fill tab.
Select blue as highlight color.
Click OK, then click OK again.


Repeat these steps, but with the formula


and red as fill color.

Right I just tried that and now The Dates are green but everything else is Red. The Dates don't color themselves properly when I change them  




Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?!AkNieIxqhd6NgS2YymFDwQp9CcWD?e=mlUIh1 Here I Deleted all sensitive data. Thanks for all your help and Patience


See the attached version. I removed green as default color, and created a whole new set of rules.