We help IT Professionals succeed at work.

Color Coding

sandramac asked
Good Day,

Trying to build a macro to perform the following:  Go to Sheet "TC MET" and in Column D, clear any conditional formatting.  Then in column D if the evaluate each cell, if it is in a DD-MMM-YY format, and is less then 75 days from the current date, then back ground color  cell Green, if the date is between 75-90, then make the backcolor yellow, if greater then 90 make back color Red.  If the data is not a date format then ignore on move to the next cell, continue this check until row 95.
Watch Question

Glenn RayExcel VBA Developer
Top Expert 2014

Is this an aging sheet?  That is, are the dates all prior to the current date?  That will affect the formula for the conditional formatting.

Also, does it matter if the date is formatted in any other way besides DD-MMM-YY?  For example, if the date is MM/DD/YYYY instead?  Again, this affects whether one is testing the date value and/or its format.
Excel VBA Developer
Top Expert 2014
I assumed this is an aging sheet and that it assumes that it is testing date values - regardless of formatting - against the current date.

The macros clears out any existing conditional formats in the range you described (column D, through row 95) and then applies three conditional formats in order.

An example file with macro is attached. EE-conditional-formatting.xlsm
Sub Reset_Conditional_Formats()
    'Create 90+ day condition - RED
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=(AND(D2<>"""",(NOW()-D2)>90))"
    Selection.FormatConditions(1).Interior.Color = RGB(255, 0, 0)
    Selection.FormatConditions(1).StopIfTrue = True
    'Create 75-90 day condition - YELLOW
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=(AND(D2<>"""",(NOW()-D2)>=75))"
    Selection.FormatConditions(2).Interior.Color = RGB(255, 255, 0)
    Selection.FormatConditions(2).StopIfTrue = True
    'Create <75 day condition - GREEN
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=(AND(D2<>"""",(NOW()-D2)<75))"
    Selection.FormatConditions(3).Interior.Color = RGB(0, 255, 0)
    Selection.FormatConditions(3).StopIfTrue = True

End Sub

Open in new window

Explore More ContentExplore courses, solutions, and other research materials related to this topic.