Trying to apply conditional formatting through VBA to an entire row based on value in column M

Question

This is the Sub I have been using. The problem is that this changes all cells in the table first to green, then Pink, then Yellow. Column M gets the conditional formatting overwritten as it progresses to each rule, so that by the end the only cells with formatting are the ones with "Weird Stuff" in them. Everything else just gets filled and isn't conditional.

The goal is to have each row change colors based on the value in column M, and as I change it, have the color update.

Sub ApplyComplexConditionalFormatting()
    Dim ws As Worksheet
    Dim tbl As ListObject
    Dim rng As Range
    Dim rng1 As Range
    Dim i As Integer
    Dim lastRow As Long
    
    Dim condition As FormatCondition
    
    ' Set the worksheet to the active worksheet
    Set ws = ActiveSheet
    
    ' Assume the table is the first table on the worksheet
    Set tbl = ws.ListObjects(1)
    
    ' Define the range to apply conditional formatting (excluding header row)
    'Set rng = tbl.DataBodyRange
    Set rng = Range("M:M")
    
    'Find length of table
        lastRow = Cells(Rows.Count, "A").End(xlUp).Row
        
    ' Clear existing conditional formatting
    rng.FormatConditions.Delete
    
    ' set subrange for rows
 For i = 2 To lastRow
    Set rng1 = Range("A" & i & ":M" & i)

    ' Apply conditional formatting rules
        ' Rule 1: "CHECK IS IN MAIL" - Fill entire row with Green
        Set condition = rng1.FormatConditions.Add(Type:=xlTextString, String:="CHECK IS IN MAIL", TextOperator:=xlContains)
        With condition
            rng1.Interior.Color = RGB(0, 255, 0) ' Green
            .StopIfTrue = False
        End With
        
    ' Rule 2: "SENT, NO RESPONSE" - Fill entire row with Pink
    Set condition = rng1.FormatConditions.Add(Type:=xlTextString, String:="SENT, NO RESPONSE", TextOperator:=xlContains)
    With condition
        rng1.Interior.Color = RGB(255, 192, 203) ' Pink
        .StopIfTrue = False
    End With
    
    ' Rule 3: "Weird Stuff" - Fill entire row with Yellow
    Set condition = rng1.FormatConditions.Add(Type:=xlTextString, String:="Weird Stuff", TextOperator:=xlContains)
    With condition
        rng1.Interior.Color = RGB(255, 255, 0) ' Yellow
        .StopIfTrue = False
    End With

 Next i

End Sub

I started over and tried the following For loop and it ALMOST works but all it does is fill the rows with the proper colors and doesn't make it so when I change the value in M it automatically updates the row color.

Sub FormattingAttempt3()

    Dim ws As Worksheet
    Dim tbl As ListObject
    Dim rng As Range
    Dim lastRow As Long
    Dim i As Long
    
    ' Set the worksheet to the active worksheet
    Set ws = ActiveSheet
    
    ' Assume the table is the first table on the worksheet
    Set tbl = ws.ListObjects(1)
    
    ' Define the range to apply conditional formatting (excluding header row)
    Set rng = tbl.DataBodyRange
    
    ' Clear existing conditional formatting
    rng.FormatConditions.Delete
    
    ' Find the last row in the table
    lastRow = tbl.ListRows.Count
    
    ' Loop through each row in the table
    For i = 1 To lastRow
        Select Case UCase(tbl.DataBodyRange.Cells(i, "M").Value)
            Case "CHECK IS IN MAIL"
                tbl.ListRows(i).Range.Interior.Color = RGB(0, 255, 0) ' Green
            Case "SENT, NO RESPONSE"
                tbl.ListRows(i).Range.Interior.Color = RGB(255, 192, 203) ' Pink
            Case "WEIRD STUFF"
                tbl.ListRows(i).Range.Interior.Color = RGB(255, 255, 0) ' Yellow
            
        End Select
    Next i
End Sub

Answer 1

Neither of your codeblocks create any conditional formatting. The latter is obvious. The former maybe less so. Let's isolate this code:

With condition
    rng1.Interior.Color = RGB(255, 255, 0) ' Yellow
    .StopIfTrue = False
End With

Only lines that start with . are acting on the With object. So the first line in the With block is changing the range itself NOT the conditional formatting rule. The solution is to delete rng1 like so:

With condition
    .Interior.Color = RGB(255, 255, 0) ' Yellow
    .StopIfTrue = False
End With

You can play with StopIfTrue and the order of the conditions to prioritize them however you want. Your code though is overkill because you don't need to loop through the rows. A quick mock-up example with xlExpression (more flexible) and applying to the whole range at once (not duplicating for each row):

Sub ApplyCondFrmt()
    Dim trgtRng As Range
    Set trgtRng = Sheet1.Range("B3:D8")
    With trgtRng.FormatConditions.Add(xlExpression, , "=NOT(ISERROR(SEARCH(""ow"", $D3)))")
        .Interior.Color = RGB(0, 255, 0)
    End With
    With trgtRng.FormatConditions.Add(xlExpression, , "=NOT(ISERROR(SEARCH(""gg"", $D3)))")
        .Interior.Color = RGB(255, 192, 203)
    End With
End Sub