How do I removed Conditional Formatting after its been applied?

Question

I have a worksheet change macro that highlights the first 8 cells in a row if the last cell contains the word "Cancelled". This works fine. However the word cancelled is in a drop down menu and if you accidently select it the macro kicks in. If you change to another word in the same cell, I would like it to remove the condition and go back to normal. Can someone help me out with this. Im sure it is something simple that I'm missing.

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo ErrHandler
   
    If UsedRange.Rows.Count > 0 Then
        If Trim(Cells(Target.Row, Target.Column)) <> "" And _
            UCase(Cells(Target.Row, Target.Column)) = "CANCELLED" Then
            
            Range(Cells(Target.Row, 1), Cells(Target.Row, 8)).Interior.Color = vbRed
            Range(Cells(Target.Row, 1), Cells(Target.Row, 8)).Font.Color = vbWhite
       
        ElseIf Trim(Cells(Target.Row, Target.Column)) <> "" And _
            UCase(Cells(Target.Row, Target.Column)) <> "CANCELLED" Then
         
                
            Cells.FormatConditions.Delete
            
        
        
        End If
    End If
ErrHandler:
    '
End Sub

Answer 1

You don't "apply" and "remove". You "apply" in both cases, just that you apply different colours.

Private Sub Worksheet_Change(ByVal Target As Range)

    Const TriggerClm    As Long = 8         ' change to suit
    Dim TriggerRng      As Range
    Dim TargetRng       As Range
    Dim IntCol          As Long
    
    ' Here the first row is 2, presuming row 1 contains captions
    Set TriggerRng = Range(Cells(2, TriggerClm), Cells(Rows.Count, TriggerClm).End(xlUp))
    
    If Not Application.Intersect(Target, TriggerRng) Is Nothing Then
        With Target
            Set TargetRng = Range(Cells(.Row, TriggerClm - 7), Cells(.Row, TriggerClm))
            If StrComp(CStr(.Value), "cancelled", vbTextCompare) Then
                TargetRng.Interior.Pattern = xlNone
                TargetRng.Font.Color = vbBlack
            Else
                TargetRng.Interior.Color = vbRed
                TargetRng.Font.Color = vbWhite
            End If
        End With
    End If
End Sub

Observe that I reasoned that a change can only be triggered if a cell in the 8th column is changed because only that cell is either "Cancelled" or not. My code's logic deviates from yours in this respect.