Invalid procedure call trying to use FormatConditions

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jay - 30 May 2008 20:35 GMT

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.

Signature

Disregard, this is so I can find my post later.
***postedbyJay***

Reply to this Message

Leith Ross - 30 May 2008 21:24 GMT

> Hello all,
>
[quoted text clipped - 30 lines]
> Disregard, this is so I can find my post later.
> ***postedbyJay***

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

Reply to this Message