VBA -highlighting rows when a condition is met

[Evie76]

Morning

I have a code that highlights rows when in column B the word red is inputted, however, I need the code to work when I press enter is that possible

Sub HighlightRowsWithRedInColumnB()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    
    ' Set the active sheet
    Set ws = ActiveSheet
    
    ' Find the last row with data in column B
    lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
    
    ' Loop through each row
    For i = 2 To lastRow ' Start from row 2 assuming headers are in row 1
        If InStr(1, ws.Cells(i, "B").Value, "red", vbTextCompare) > 0 Then
            ' Highlight the entire row from column A to O
            ws.Rows(i).Range("A1:O1").Interior.Color = RGB(255, 0, 0) ' Red color
        End If
    Next i
End Sub

 

[Kaper]

Your code is in standard module.
You can add in the Sheet module the code, which is fired after change in a cell occurs (if you just press enter in an empty cell, excel also "thinks" the content has changed):

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Columns("B:B")) Is Nothing Then
  Call HighlightRowsWithRedInColumnB
End If
End Sub

This code reacts only if the change (enter pressed) was in column B (where the main code checks for "red" presence. If you want to react on enter pressed anywhere - just remove "IF Not ... " and "End If" lines

If you want the code to work in eny sheet in workbook insert it in Thisworkbook module, but then the name of sub will be:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Final comment: Your code does not react on "red" removal. So the line which was red, because of "red" in B remains red after "red" is removed from B.

 

[Evie76]

Perfect thank you