VBA Conditional Formatting not Applying

[Nick72310]

I am having the issue of my Conditional Formatting (CF) not applying through VBA. Everything is coded correctly, in the respect that I don not receive any errors. If I run the marco and then go into the worksheet, select Conditional Formatting, click 'edit rule' > ok > Apply, without changing anything, it works. For some reason the CF is not being applied. Interestingly enough, if I save the document after running the macro and then reopen it, the CF works properly until I run the code again. I need it to work of the fly though.

The only thing I can think of that could be messing with the code is that the formula, when entered into a cell, needs to be entered with Ctrl+Shift+Enter.


For i = 1 To NumberOfRowsToBeAdded
DR = Destn.Row
With Cells(DR + i - 1, 9)

.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="=I" & DR + 1 & "<>INDEX(Usage_Rate,MATCH(1,((C" & DR & "=STD_Op_Code)*(F" & DR + i - 1 & "=Res_Seq_No)),0))"

.FormatConditions(.FormatConditions.Count).SetFirstPriority
With Cells(DR + i - 1, 9).FormatConditions(1).Font
'Black
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
End With

With Cells(DR + i - 1, 9).FormatConditions(1)
.Interior.Color = RGB(255, 255, 0)
End With
Cells(DR + i - 1, 9).FormatConditions(1).StopIfTrue = False
End With
Next i

[p45cal]

The only thing I can think of that could be messing with the code is that the formula, when entered into a cell, needs to be entered with Ctrl+Shift+Enter.I'm fairly sure that all CF formulae are treated as if CSE-entered.
My main question would be is Calculation set to Automatic on that sheet? (Pressing F9 on the keyboard should recalculate it - does the formatting appear if you do that?

Otherwise, withoutknowing what Usage_Rate, STD_Op_Code and Res_Seq_No are it's pretty much impossible to guess reliably.
Attaching a workbook displaying this behaviour would be best.

[p45cal]

Nick72310 sent me a file privately with this occurring. There was nothing obviously wrong with the CF formula. There was no format showing after the code had deleted and re-applied CF, however, saving the file then re-opening it would allow the CF to show on the sheet.
After trying many things, all of which failed, simplifying the formula did the trick, at my end at least.
.FormatConditions.Add Type:=xlExpression, Formula1:="=SUMPRODUCT((Usage_Rate=I" & DR + i - 1 & ")*($C$" & DR & "=STD_Op_Code)*(F" & DR + i - 1 & "=Res_Seq_No))<1"

[Nick72310]

That formula works perfect! Thank you p45cal!