How to set conditional format with vba?
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:="=B2C2"
..FormatConditions(1).Interior.ColorIndex = 3
End With
End With
End Sub
--
Regards,
Tom Ogilvy
"deko" wrote in message
...
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....
|