CODEWITHSUNDEEP
excelconditional-formattingvba
Pascal
Pascal

Reputation: 14042

conditional formatting and vba

In a macro I am using the following code:

With Range("T2:X31")
    .FormatConditions.Delete
    .FormatConditions.Add Type:=xlExpression, Formula1:="=$R2=0"
    .FormatConditions.Add Type:=xlExpression, Formula1:="=$AE2"
    .FormatConditions.Add Type:=xlExpression, Formula1:="=$AF2"
    .FormatConditions(1).Interior.Color = RGB(216, 216, 216)
    .FormatConditions(1).Font.Color = RGB(216, 216, 216)
    .FormatConditions(2).Interior.Color = RGB(255, 40, 40)
    .FormatConditions(2).Font.Color = RGB(255, 255, 255)
    .FormatConditions(3).Interior.Color = RGB(255, 128, 0)
    .FormatConditions(3).Font.Color = RGB(0, 0, 0)
End With

What I would like to get in excel when I check the conditional formatting rules is (entered manually):

enter image description here

What I get instead is

enter image description here

where everything is ok except the row (1046053 instead of 2). How should I enter the cell reference to get the right formula.

Upvotes: 2

Views: 351

Answers (1)

Alex Butenko
Alex Butenko

Reputation: 3774

Try to select A2 before to set conditions:

Range("A2").Select

I never used VBA, but I have suspicion that formulas in its conditional formatting related to the current selection.

I think it's even better to select the range you actually set conditions for, as you do it from interface:

Range("T2:X31").Select

Upvotes: 1

Related Questions