Conditional Formatting in Excel VBA

[1968rm]

I am trying to delete all conditional formatting from a sheet, and add new formatting. I use the following macro (it's recorded) but when I run it, it gives me an error by .ThemeColor = xlThemeColorDark1 towards the end of the macro. The first conditional format highlights the cell yellow, and the second one changes the font to white.

Sub PLConditionalFormatting()
    Cells.FormatConditions.Delete
    Columns("L:L").Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=A1=0"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Columns("F:L").Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=B1=0"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Font
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
End Sub

 

[Jerry Sullivan]

Welcome to the Board,

I think the problem has to do with the way Column L is overlapped in the Selection ranges for the two rules. If you flip the sequence in which you define the two rules, it will work.

Here is version that is cleaned up a bit to remove some of the unneeded parts added by the macro recorder:

Sub PLConditionalFormatting()
    Cells.FormatConditions.Delete
 
    With Columns("F:L")
        .FormatConditions.Add Type:=xlExpression, Formula1:= _
            "=B1=0"
        With .FormatConditions(.FormatConditions.Count)
            .SetFirstPriority
            With .Font
                .ThemeColor = xlThemeColorDark1
                .TintAndShade = 0
            End With
            .StopIfTrue = False
        End With
    End With
 
    With Columns("L:L")
        .FormatConditions.Add Type:=xlExpression, Formula1:= _
            "=A1=0"
        With .FormatConditions(.FormatConditions.Count)
            .SetFirstPriority
            With .Interior
                .PatternColorIndex = xlAutomatic
                .Color = 65535
                .TintAndShade = 0
            End With
            .StopIfTrue = False
        End With
    End With
 
    ActiveCell.Font.ThemeColor = xlThemeColorDark1
End Sub

I believe the code doesn't work when the rule for Column L is defined first, because at the point the code reaches the second instance of:

With Selection.FormatConditions(1).Font

...this expression is referring to one rule for part the Selection and a different rule for the rest of the Selection. Not positive about that reason for the error, but I'm positive it works when the order is flipped.

 

[1968rm]

@JS411 Thanks! works perfectly!

My problem was with the font only, if I did the same as my original but put a fill in the formatting, it worked, but not with a font color. I can't figure that out. But now with your macro - a breeze!
Now my question: Why did you finish off with this?

ActiveCell.Font.ThemeColor = xlThemeColorDark1

What does it accomplish?

 

[Jerry Sullivan]

Oops..that last line I left in my mistake. When initially trying to figure out the reason for the error, I ran that as a test to isolate it from the Conditional Formatting.

Glad this worked for you. I just realized in flipping the order of the two rules you would need to make sure that it applied the intended rule to Column L.

Cheers!

 

[1968rm]

@JS411 I just realized a weird thing. The formulas I used were "=$U1=TRUE" for the white font and "=$Y1=TRUE" for the yellow fill, but the conditional formatting changed that to =$U1048558=TRUE for the white font and =$Y1048558=TRUE for the yellow fill. Would you know why that happens and how I can correct it? It throws off my formatting to different rows... Thanks for your help!

 

[1968rm]

Figured it out. Cell A1 needs to be selected at the time the macro is run. Works like a breeze now!!

 

[welshgasman]

Hi all,
Came upon this thread whilst trying to find out why Excel was putting in random row number in the formatting formula
I have this code that I adapted from the net, and have just added a comment about Cell A1

' Cell A1 needs to be selected for the formatting to work, else it puts in a random row number
    'Range("A1").Select
    'With Range("G3:G" & lLastRow - 2).FormatConditions.Add( _
    '    Type:=xlExpression, _
    '    Formula1:="=NOT(ISBLANK($I3))")
    '    .Interior.Color = RGB(161, 212, 144)
     '   '.Font.Color = RGB(0, 97, 0)
    'End With

Here I am trying to highlight cells in column G if corresponding cell in column I is not blank (it will have a date)
The workbook is created from scratch and data pasted, formatted and saved.
On my first attempts Excel was doing the same thing, changing $I3 to $I1048576, when the last cell is only I&lLastRow

I know which cells have been populated with data, so know the range to apply to (that seems to stay correct?), but how do I get the condiitional formula to address the correct cell?
Code is commented out for now, hence the '
TIA

 

[Fazza]

change to R1C1 notation in the formula1:=

then no need to select cells

 

[welshgasman]

Thank you.
I'll give that a go. It would also help when columns are moved.
Off to find the syntax for that.

Thanks again.

 

[Fazza]

suggest set Excel to R1C1 notation (tools. options, etc) and generate the formula. copy it to VBA. reset to normal notation

after a little familiarity R1C1 is easy enough