Conditional formatting using VBA

[wrecclesham]

Could someone please help me to create a VBA script which applies the following three conditional formatting rules?

Rule 1

Rule 2

Rule 3

 

[Michael M]

Did you try using the macro recorder ??
I managed this, but I'm not sure about the formulas

Sub Macro1()
With Range("D2:D11")
    .FormatConditions.Add Type:=xlExpression, Formula1:="=ISBLANK($D$2:$D$11)"
    .FormatConditions(Range("D2:D11").FormatConditions.Count).SetFirstPriority
    .FormatConditions.Add Type:=xlExpression, Formula1:="=OR(IFERROR(TODAY()-D2,0)>=120,ISTEXT(D2))"
    .FormatConditions(Range("D2:D11").FormatConditions.Count).SetFirstPriority
        With Range("D2:D11").FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = 5296274
        End With
    .FormatConditions.Add Type:=xlExpression, Formula1:="=TODAY()-D2:D11<120"
    .FormatConditions(Range("D2:D11").FormatConditions.Count).SetFirstPriority
        With Range("D2:D11").FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = 10066431
        End With
End With
End Sub

 

[wrecclesham]

Thanks a lot for that code!

I'm trying to avoid using the macro recorder in this particular case as I want to have clean code that I can use to learn from in the future.

 

[Michael M]

OK, so now use the recorder and compare the 2 codes if you wish to learn.....and I can bet someone will come up with even better code !!