Multiple Conditional Formatting and Color

[iamtherascalking]

What's wrong with the following?
It keeps getting caught at

.FormatConditions(1).Font.Color = RGB(255, 0, 0)

Sub Macro1()

    Cells.FormatConditions.Delete

    Columns("A:A").Select
    
    With Selection

        .FormatConditions.Add Type:=xlExpression, Formula1:="=OR($B1=""PASS"",$B1=""FAIL"")"
        
        .FormatConditions(Selection.FormatConditions.Count).SetFirstPriority

            With .FormatConditions(1).Font
                .Bold = True
                .Italic = False
                .TintAndShade = 0
            End With

        .FormatConditions(1).StopIfTrue = False

    End With

'=================================

    Columns("A:E").Select
    
    With Selection
    
        .FormatConditions.Add Type:=xlExpression, Formula1:="=OR($B1=""FAIL"",$B1=""SCRAP"")"
        
        .FormatConditions(Selection.FormatConditions.Count).SetFirstPriority

        .FormatConditions(1).Font.Color = RGB(255, 0, 0)
        .FormatConditions(1).Font.TintAndShade = 0

        .FormatConditions(1).StopIfTrue = False

    End With

End Sub

 

[jasonb75]

Try

Sub Macro1()
    Cells.FormatConditions.Delete
    Columns("A:A").Select
    
    With Selection
        .FormatConditions.Add Type:=xlExpression, Formula1:="=OR($B1=""PASS"",$B1=""FAIL"")"
        
        .FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
            With .FormatConditions(1).Font
                .Bold = True
                .Italic = False
                .TintAndShade = 0
            End With
        .FormatConditions(1).StopIfTrue = False
    End With
'=================================
    Columns("A:E").Select
    
    With Selection
    
        .FormatConditions.Add Type:=xlExpression, Formula1:="=OR($B1=""FAIL"",$B1=""SCRAP"")"
        
    .FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
            With .FormatConditions(2).Font
            .TintAndShade = 0
            .Color = RGB(255, 0, 0)
            
            End With
        .FormatConditions(2).StopIfTrue = False
    End With
        
        
End Sub

Untested with live data but doesn't error like your original so hopefully works ok.

 

[iamtherascalking]

Doesn't work for me, sorry. Still getting a "1004" error, but now at the second TintAndShade line.

 

[jasonb75]

Ok, now I'm having some unusual problems, I ran a test on this after posting and it worked, now I just tried it again, it works with a new file but not a saved one.

I'll have a look again see if I can find why it does that.

 

[jasonb75]

Just out of curiosity, which version of excel do you have and which file extension on your workbook?

Seems to work ok for excel 03 files or if added to 07 xlsx type, but not excel 07 xlsm, which is even more confusing.

 

[iamtherascalking]

I'm using 07, but its not saved at all; just a new workbook. It doesn't work if I save as xlsm or xlsx(and run from the xlsm) either.

 

[iamtherascalking]

They work separately. If you isolate the top part and run it(with Cells.FormatConditions.Delete at the top), it works. Then isolate the bottom and run it(with Cells.FormatConditions.Delete at the top), it works.

But together they don't work.

 

[jasonb75]

I found the same thing, but mine runs ok with the non xlsm files so that's the real confusing part.

Just to make sure I'm reading your formula and code right

You want column A bold if column B says Pass or Fail
And you want the row A:E red if B says fail or scrap ?

 

[iamtherascalking]

yes, u are correct. so you got it working in a xlsx? or in 03?

 

[jasonb75]

neither, when it does work the results are dirrerent, it's red for pass and normal for the others, that's why i wanted to check before i try and correct it.

only just noticed that part of the problem.

But now for the ultimate error!

I just recorded a macro while setting the conditions manually, and even that gives error when it runs!

Excel doesn't like the macro it created!