Conditional Formatting Not Setting Format Conditions-CodePudding

I am trying to write some code that changes the cell fill colour of specified groups of cells if a formula is correct. At the moment only some of the groups of cells are formatting as I intend.

Concerning the groups of cells that are not formatting as desired - when manually inspecting the Conditional Formatting Manager - the conditional formatting correctly contains the groups of cells that the formatting should be applied to, but the cell formatting has not been added. I find this surprising as at the moment the intended cell formatting is the same for all groups. I would be grateful as to some suggestions as to what I should do to solve this issue! Thank you for any comments.

Code Below: My main program creates groups of cells that are to be formatted - these cells are stored in a variable (pathstring) and then passed to the subroutine shown. (So all the conditionally formatted groups of cells should be red, but some groups have no colour formatting at all.)

Sub ConditionalFormattingExample(pathstring As String)

'Define Range
Dim MyRange As Range
Set MyRange = Range(pathstring)

'Apply Conditional Formatting

MyRange.FormatConditions.Add Type:=xlExpression, Formula1:="=$AN$16=5"
MyRange.FormatConditions(1).SetFirstPriority
MyRange.FormatConditions(1).Interior.Color = RGB(255, 0, 0)
MyRange.FormatConditions(1).StopIfTrue = False

End Sub

CodePudding user response：

If this is the only conditional formatting applied then you might want to first delete any existing CF:

Sub ConditionalFormattingExample(pathstring As String)
    'ideally specify a worksheet here...
    With Range(pathstring).FormatConditions
        .Delete
        With .Add(Type:=xlExpression, Formula1:="=$AN$16=5")
            .SetFirstPriority
            .Interior.Color = RGB(255, 0, 0) 'or vbRed
            .StopIfTrue = False
        End With
    End with
End Sub