It worked okay for me if lr was a valid value, i.e. 0, although testing a
value to be greater than a range seems odd, and the formula doesn't work
anyway.

Also, best to delete Formatcondition first

Overall, try this

With Workbooks(strXlSFile).Worksheets(sn(i)).Range _
("B2:B" & lr)
..FormatConditions.Delete
..FormatConditions.Add _
Type:=xlExpression, Operator:=xlGreater, Formula1:="=B2$C$2:$C$" & lr
..FormatConditions(1).Interior.ColorIndex = 3
End With

although the testr still doesn't make sense to me, but I don't know what you
are trying to do.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"deko" wrote in message
m...
Can I assign a range to a formula when setting a conditional format?

'compare values in column B to adjacent values in column C
'(lr = last row)
xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Rang e _
("B2:B" & lr).FormatConditions.Add _
Type:=xlCellValue, Operator:=xlGreater, Formula1:="C2:C" & lr
'apply formatting
xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Rang e _
("B2:B" & lr).FormatConditions(1).Font.ColorIndex = 11

This code results in:
Error Number 5: Invalid procedure call or argument

The problem, I think, is with:
Formula1:="C2:C" & lr

How do I assign conditional formatting to each cell in column B?

Thanks in advance.