Re: How to set conditional format with vba?

Tech-Archive recommends: Fix windows errors by optimizing your registry

Sub HIJ()
Dim sn(0 To 1)
sn(0) = "Sheet1"
strXlSFile = ActiveWorkbook.Name
lr = 20
i = 0

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

--
Regards,
Tom Ogilvy

"deko" <deko@xxxxxxxx> wrote in message
news:nB69e.3195$J12.671@xxxxxxxxxxxxxxxxxxxxxxxxxxxxx
> > 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.
>
> The idea is to compare each value in each row of column B to each value in
> each adjacent row in column C.
>
> For example:
>
> If B2 > C2 then font is red. And so on - comparing B3 to C3, B4 to C4,
etc.
> until the end of the row.
>
> I tried "=B2>$C$2:$C$" & lr - but no luck....
>
>


.

Quantcast