Nick72310
07-27-2016, 01:31 PM
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
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