Add color to cell when a cell contains a set of words.

[Livin404]

Greetings I am hope to adjust the macro so that it will highlight the row between Columns A:E. I want this particular Maco to do it's job any time "CUST & AG REQ" occurs anywhere in Column E. "CUST & AG REQ" can appear anywhere in Column E, and I don't want to list all the variances that it can occure.

Sub Local_BACKGROUND()
    Sheets("72 Hr").Select
    endrow = Range("E" & Rows.Count).End(xlUp).Row
    
    For Each cell In Range("E3:E" & endrow)
        Select Case cell.Value
            Case "CUST & AG REQ"
                Range(Cells(cell.Row, "A"), Cells(cell.Row, "E")).Interior.ColorIndex = 45
            
        End Select
    Next cell
End Sub

 

[Joe4]

I would do it like this:

Sub Local_BACKGROUND()

    Dim endrow As Long
    Dim cell As Range

    Sheets("72 Hr").Select
    endrow = Range("E" & Rows.Count).End(xlUp).Row
   
    For Each cell In Range("E3:E" & endrow)
        If InStr(cell, "CUST & AG REQ") > 0 Then
            Range(Cells(cell.Row, "A"), Cells(cell.Row, "E")).Interior.ColorIndex = 45
        End If
    Next cell
   
End Sub

Note that this can also be done pretty easily just using Conditional Formatting (no VBA required).

 

[Livin404]

I would do it like this:

Sub Local_BACKGROUND()

    Dim endrow As Long
    Dim cell As Range

    Sheets("72 Hr").Select
    endrow = Range("E" & Rows.Count).End(xlUp).Row
 
    For Each cell In Range("E3:E" & endrow)
        If InStr(cell, "CUST & AG REQ") > 0 Then
            Range(Cells(cell.Row, "A"), Cells(cell.Row, "E")).Interior.ColorIndex = 45
        End If
    Next cell
 
End Sub

Note that this can also be done pretty easily just using Conditional Formatting (no VBA required).

Thank you, I didn't think it worked at first but forgot to plug in on my Run all Macro. It's perfect as of righ now.

 

[Livin404]

I would do it like this:

Sub Local_BACKGROUND()

    Dim endrow As Long
    Dim cell As Range

    Sheets("72 Hr").Select
    endrow = Range("E" & Rows.Count).End(xlUp).Row
  
    For Each cell In Range("E3:E" & endrow)
        If InStr(cell, "CUST & AG REQ") > 0 Then
            Range(Cells(cell.Row, "A"), Cells(cell.Row, "E")).Interior.ColorIndex = 45
        End If
    Next cell
  
End Sub

Note that this can also be done pretty easily just using Conditional Formatting (no VBA required).

I thought about that, but I have a macro that clears everything out including all conditional formats.

 

[Joe4]

I thought about that, but I have a macro that clears everything out including all conditional formats.

You are welcome.

Yes, I figured it might be something like that, but just wanted to mention it, just in case.