VBA conditional formatting not applying

Question

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

Try to record a macro and see which methods are called when you do the edit rules thing. — Seb
I did and the methods called were all those included in the two inner with blocks, but no joy — user3760185

Radek Radek · Accepted Answer · 2014-11-20T15:10:45

0

votes

Try ThisWorkbook.RefreshAll or Application.Calculate

VBA conditional formatting not applying

1 Answers