conditional formatting and vba

Question

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

What I get instead is

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

EDIT - I actually just ran your code and it worked perfectly for me! ... I have a question. Which cells do you have active or selected when this part of the code runs? — Scott Holtzman
– Scott Holtzman, Commented Jan 7, 2016 at 16:26
Is this Excel 2007? The CF was a little buggy in that release. What significance does 2523 have? Typically, this occurs when deleting rows as the CFR formula attempts to compensate for the deleted rows by rewriting the formula to suit and the 'tin can' effect comes into play when adjusting the row references goes lower than 1 and ends up on the bottom of the worksheet. — user4039065
– user4039065, Commented Jan 7, 2016 at 16:57

Alex Butenko · Accepted Answer · 2016-01-07 16:43:05Z

1

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

edited Jan 7, 2016 at 16:43

answered Jan 7, 2016 at 16:37

Alex Butenko

3,8143 gold badges39 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Pascal Over a year ago

I have added .Select before .FormatConditions.Delete and it works well. Thank you, I didn't find any clue about this in the documentation I have read.

Collectives™ on Stack Overflow

conditional formatting and vba

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related