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
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
|