View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
deko[_3_] deko[_3_] is offline
external usenet poster
 
Posts: 62
Default How to set conditional format with vba?

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.