How to empty the cell content if multiple data mat

Question

I have an Excel worksheet where I want to clear cell data if multiple matches are found within a range.

Example image

If matches are found like SUP or AL then clear the content and color the cell corresponding to the match found (e.g SUP - yellow, AL - Red). I have used Below code it is not working.

Dim l As Long, lRow As Long
With Sheets("Sheet1")
    lRow = .Range("A" & Rows.Count).End(xlUp).Row
    For l = 1 To lRow
    For Each c In Range("A:AE")
    If c.Value = "SUP" Then
    c.Value = ""
    End If
    Next l
End With

Answer 1

Give this a try:

Dim r As Range, c As Range
Dim sh As Worksheet

For Each sh In Thisworbook.Worksheets '// loop on all worksheet //
  With sh

    Set r = .Range("A1:AE7") '// adjust to your actual range //
    For Each c In r
      With c
        Select Case UCase(.Value2) '// non case sensitive search //
        Case "SUP": .ClearContents: .Interior.Color = RGB(255, 255, 0)
        Case "AL": .ClearContents: .Interior.Color = RGB(255, 0, 0)
        End Select
      End With
    Next

  End With
Next

Edit1: Added looping through all worksheets and the non-case sensitive search. Not tested, but should give you idea on how to do it.

Answer 2

This one should work

Option Explicit

Sub test()

Dim c As Range, arr As Range

Set arr = Range("A7:AE7")

For Each c In arr
    If InStr(1, c, "SUP") Or InStr(1, c, "AL") Then
       c.Clear
    End If
Next

End Sub