'VBA conditional formatting not applying

I've got a routine that loops through all columns on all worksheets in a workbook to apply a conditional formatting rule to all of them. The rule is saved on each column fine and the rule I've used works, but the colour doesn't change after the routine is run. I can then go into the worksheet, select conditional formatting, click 'edit rule' > 'ok' > 'ok' and the formatting then updates on the worksheet, without having to change anything about the rule. What am I missing to get the rule to actually make a change to the worksheet?

For Each ws In ThisWorkbook.Worksheets

If ws.Name Like "*Management*" Then Exit Sub

lastRow = ws.UsedRange.Row + ws.UsedRange.Rows.Count - 1
lastCol = ws.UsedRange.Column + ws.UsedRange.Columns.Count - 1

ws.Cells.FormatConditions.Delete

For col = 1 To lastCol

    Set rng = ws.Range(ws.Cells(2, col), ws.Cells(lastRow, col))
    formulaStr = "=BITWISE_AND(2^INDEX(" & wsSchema.Name & "!$" & schemaLastCol & "$2" & _
        ":$" & schemaLastCol & "$" & schemaLastRow & ", MATCH(1, (" & wsSchema.Name & "!$A$2:$A$" & _
        schemaLastRow & "=""" & ws.Name & """)*(" & wsSchema.Name & "!$B$2:$B$" & schemaLastRow & "=$" & _
        XLCol(col) & "$1),0)), INDEX($" & XLCol(lastCol) & "$1:$" & XLCol(lastCol) & "$" & lastRow & ",ROW()))"

    With rng
        .FormatConditions.Add xlExpression, , formulaStr & " = 0"
        With .FormatConditions(1)
            .Interior.PatternColorIndex = xlAutomatic
            .Interior.Color = RGB(255, 0, 0)
            .Interior.TintAndShade = 0
            .StopIfTrue = False
            .SetFirstPriority
        End With
        .FormatConditions.Add xlExpression, , formulaStr & " > 0"
        With .FormatConditions(2)
            .Interior.PatternColorIndex = xlAutomatic
            .Interior.Color = RGB(0, 255, 0)
            .Interior.TintAndShade = 0
            .StopIfTrue = False
            .SetFirstPriority
        End With
    End With

Next

vba conditional-formatting

Solution 1:^[1]

Try ThisWorkbook.RefreshAll or Application.Calculate

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1	Radek

'VBA conditional formatting not applying

Solution 1:[1]

Sources

Related Questions

Solution 1:^[1]