Re: How to set conditional format with vba?

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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" <deko@xxxxxxxx> wrote in message
news:L769e.3194$J12.2794@xxxxxxxxxxxxxxxxxxxxxxxxxxxxx
> 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)).Range _
> ("B2:B" & lr).FormatConditions.Add _
> Type:=xlCellValue, Operator:=xlGreater, Formula1:="C2:C" & lr
> 'apply formatting
> xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Range _
> ("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.
>
>


.

Quantcast