Invalid procedure call trying to use FormatConditions

[Jay]

Hello all,

I'm trying to set the conditional formats for a whole group of cells, but I
keep getting a error.

Error: Run-time error '5': Invalid procedure call or argument.

I can't understand what is causing the error. Below is my code, I hope
someone can help me solve this. No matter how much I look at it, I can't see
the issue. Nothing I've tried has worked.

I don't set the value of the formatting in one shot (using range) because
each row the formula is different. I did try doing it using Range but it
didn't solve the problem.

maxRow = wrk.UsedRange.Rows.Count
For rowNum = 3 To maxRow
For colNum = 50 To 52
strCellFormula = "=($AX$" & rowNum & "=0)"
With wrk.Cells(rowNum, colNum)
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Operator:=xlNotEqual,
Formula1:=strCellFormula
.FormatConditions(1).Font.ColorIndex = ZERO_VALUE_CELLS_COLOR_INDEX
End With
Next colNum
Next rowNum

I'd appreciate any help I can get.

Thanks.

 

[Leith Ross]

Hello all,

I'm trying to set the conditional formats for a whole group of cells, but I
keep getting a error.

Error: Run-time error '5': Invalid procedure call or argument.

I can't understand what is causing the error. Below is my code, I hope
someone can help me solve this. No matter how much I look at it, I can't see
the issue. Nothing I've tried has worked.

I don't set the value of the formatting in one shot (using range) because
each row the formula is different. I did try doing it using Range but it
didn't solve the problem.

maxRow = wrk.UsedRange.Rows.Count
For rowNum = 3 To maxRow
For colNum = 50 To 52
strCellFormula = "=($AX$" & rowNum & "=0)"
With wrk.Cells(rowNum, colNum)
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Operator:=xlNotEqual,
Formula1:=strCellFormula
.FormatConditions(1).Font.ColorIndex = ZERO_VALUE_CELLS_COLOR_INDEX
End With
Next colNum
Next rowNum

I'd appreciate any help I can get.

Thanks.

Hello Jay,

I made a few changes to you code so it would run it on my computer.
The macro works. Check you variable assignments, like for wrk, and be
sure they are assigned to valid objects. Here is the code I used...

Sub Test()

maxrow = ActiveSheet.UsedRange.Rows.Count
For rowNum = 3 To maxrow
For colNum = 50 To 52
strCellFormula = "=($A$X" & rowNum & "=0)"
With ActiveSheet.Cells(rowNum, colNum)
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression,
Formula1:=strCellFormula
.FormatConditions(1).Font.ColorIndex = 3 'Red
End With
Next colNum
Next rowNum

End Sub

Sincerely,
Leith Ross