|For Red, a date in E4 at least 12 months prior to the current date, use:|
For Yellow, a date in E4 between 6 months and 12 months prior to the current date, use:
If you want Green for dates less than 6 months old, use:
Note: You should apply the rules in the order listed in this post, i.e >12, >6, >-6. When using CF, if more than one rule applies the same kind of formatting, such as a Fill color, the first rule that is TRUE take precedence. If you have the >6 month rule first, it will fill all cells 6 months or older with Yellow and not continue checking for dates that are 12 months older.
The reason I added IF(E4<>""...) is to allow you to leave a cell blank without the CF being applied. Without the IF(E4<>""...) Excel may consider a blank cell to be 1/0/1900 and apply the "older than 12 months" CF.
To apply the CF to a range of cells, select the entire range and use the rules suggested above. The rules will be applied to each cell in the range individually.
I don't think you want to apply the CF across the entire sheet, unless you will have dates in every cell. You should only apply the CF to the cells that you need to check.
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.