Conditional Formatting

Question

The Macro below&nbsp;works perfectly when column “L” is filled with numbers BUT it will actually be filled with dates, and I cannot work out how to make it work when column L has dates in it. Can anyone help please?&nbsp;'Sub &nbsp;Critical Dates()&nbsp;&nbsp;&nbsp; Dim rg As Range&nbsp;&nbsp;&nbsp; Dim iset As IconSetCondition&nbsp;&nbsp;&nbsp; Set rg = Range("L1", Range("L1").End(xlDown))'Delete previous formatting conditions&nbsp;&nbsp; &nbsp;Sheets("CriticalDates").Select&nbsp;&nbsp;&nbsp; rg.FormatConditions.Delete&nbsp;&nbsp;&nbsp;‘Choose Traffic Lights icon set&nbsp;&nbsp;&nbsp; Sheets("CriticalDates").Select&nbsp;&nbsp;&nbsp; Columns("L:L").Select&nbsp;&nbsp;&nbsp; Selection.FormatConditions.AddIconSetCondition&nbsp;&nbsp;&nbsp; Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority&nbsp;&nbsp;&nbsp; With Selection.FormatConditions(1)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; .ReverseOrder = False&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; .ShowIconOnly = False&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; .IconSet = ActiveWorkbook.IconSets(xl3TrafficLights1)&nbsp;&nbsp;&nbsp; End With‘Set AMBER Traffic Light Condition&nbsp;&nbsp;&nbsp; With Selection.FormatConditions(1).IconCriteria(2) &nbsp;&nbsp;&nbsp;&nbsp;'IconCriteria(2) is AMBER traffic light&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; .Type = xlConditionValueNumber&nbsp; &nbsp;&nbsp; 'sets xlConditionValue to a number&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; .Operator = 7&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'xlGreaterEqual&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; .Value = 30 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'i.e. between this number (30) and 60 &nbsp;'(60 is the GREEN no. below)&nbsp;&nbsp;&nbsp; End With‘Set GREEN Traffic Light Condition&nbsp;&nbsp;&nbsp; With Selection.FormatConditions(1).IconCriteria(3) &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'IconCriteria(3) is GREEN traffic light&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; .Type = xlConditionValueNumber&nbsp;&nbsp; 'sets xlConditionValue to a number&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; .Operator = 7&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;'xlGreaterEqual&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; .Value = 60 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'i.e. from 60 up&nbsp; - formula would in effect be &lt;=61&nbsp;&nbsp;&nbsp; End WithEnd Sub&nbsp;&nbsp;I thought the macro would work with DATEDIF function to try to get a number: DATEDIF(TODAY(),L2,”D”) or DATEDIF(NOW(),L2,”D”) in the hope the value would be return a number (i.e. the number of days between L2 and today), but I couldn’t get the right syntax for the macro to work. I also tried using xlConditionFormula instead of xlConditionNumber but couldn’t work out how to do that. Can anyone please help to get the macro to achieve what I want (assuming it is possible).&nbsp; Many thanks. Les King

hansvogelaar · Accepted Answer

LesKing&nbsp;
Try this:
Sub CriticalDates()
    Dim rng As Range
    Dim isc As IconSetCondition
    Set rng = Range("L1", Range("L1").End(xlDown))
    rng.FormatConditions.Delete
    Set isc = rng.FormatConditions.AddIconSetCondition
    With isc
        .ReverseOrder = False
        .ShowIconOnly = False
        .IconSet = ActiveWorkbook.IconSets(xl3TrafficLights1)
        With .IconCriteria(2)
            .Type = xlConditionValueFormula
            .Value = "=TODAY()+30"
            .Operator = xlGreaterEqual
        End With
        With .IconCriteria(3)
            .Type = xlConditionValueFormula
            .Value = "=TODAY()+60"
            .Operator = xlGreaterEqual
        End With
    End With
End Sub

Forum Discussion

Conditional Formatting

Share

Resources