It works because you have A1 selected. If you selected Z26 and ran your
macro, you would get much different results. Conditional format formulas
containing relative references, when entered are sensitive to the location
of the activecell.

I gave you code that accounted for this, but you claim you couldn't get it
to work even though it was tested and was doing what you want.



--
Regards,
Tom Ogilvy

"deko" wrote in message
.. .
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

I played around with that but was unsuccessful.

I got this to work, but don't understand why I need to use A1 and B1
instead
of A2 and B2 in the format condition formula. Why does the formula need
to
be offset one row?

xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Rang e _
("B2:B" & lr).FormatConditions.Delete

xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Rang e _
("B2:B" & lr).FormatConditions.Add _
Type:=xlExpression, Formula1:="=A1<B1"

xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Rang e _
("B2:B" & lr).FormatConditions.Add _
Type:=xlExpression, Formula1:="=A1B1"

xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Rang e _
("B2:B" & lr).FormatConditions.Add _
Type:=xlExpression, Formula1:="=A1=B1"

xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Rang e _
("B2:B" & lr).FormatConditions(1).Font.ColorIndex = 11
xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Rang e _
("B2:B" & lr).FormatConditions(2).Font.ColorIndex = 9
xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Rang e _
("B2:B" & lr).FormatConditions(3).Font.ColorIndex = 10