Conditional Formatting

The Macro below 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 h...

Formulas and Functions

HansVogelaar

Apr 06, 2023

LesKing

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

HansVogelaar

MVP

Apr 06, 2023

LesKing

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

LesKing
Brass Contributor
Apr 06, 2023
HansVogelaar

Hi Hans,

That worked perfectly - and was much more straightforwad than the things I was trying!!
Thank you so much!
Les King

Forum Discussion

Conditional Formatting

Resources