How to "convert" Conditional Format in VBA?

[Eloise T]

This is the Conditional Format specifications that I need to duplicate in VBA code:



This is what I've got. It doesn't error out, but it doesn't work either.

With Ws.Range("D3:D250")
     .FormatConditions.Add Type:=xlExpression, Formula1:=">=6"
End With

The problem as I see it is indicating the Cell Value in the VBA code ( Formula1:=">=6" )

Thanks for your help in advance.

 

[Ajesh]

Try this:

With ws.Range("D3:D250")
    .FormatConditions.Add Type:=xlCellValue, Operator:=xlGreaterEqual _
        , Formula1:="=6"
    .FormatConditions(.FormatConditions.Count).SetFirstPriority
    With .FormatConditions(1).Interior
        .Pattern = xlPatternLinearGradient
        .Gradient.Degree = 0
        .Gradient.ColorStops.Clear
    End With
    With .FormatConditions(1).Interior.Gradient.ColorStops.Add(0)
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    With .FormatConditions(1).Interior.Gradient.ColorStops.Add(1)
        .Color = 14171890
        .TintAndShade = 0
    End With
    .FormatConditions(1).StopIfTrue = False
End With

And hats off to your signature text. Loved it.

All the best! Merry Xmas.

BR/Ajesh

 

[Eloise T]

Try this:

With ws.Range("D3:D250")
    .FormatConditions.Add Type:=xlCellValue, Operator:=xlGreaterEqual _
        , Formula1:="=6"
    .FormatConditions(.FormatConditions.Count).SetFirstPriority
    With .FormatConditions(1).Interior
        .Pattern = xlPatternLinearGradient
        .Gradient.Degree = 0
        .Gradient.ColorStops.Clear
    End With
    With .FormatConditions(1).Interior.Gradient.ColorStops.Add(0)
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    With .FormatConditions(1).Interior.Gradient.ColorStops.Add(1)
        .Color = 14171890
        .TintAndShade = 0
    End With
    .FormatConditions(1).StopIfTrue = False
End With

And hats off to your signature text. Loved it.

All the best! Merry Xmas.

BR/Ajesh

Outstanding design and results, BR/Ajesh!