Highlight an Entire Row in Excel Based on a Cell Value using VBA Conditional Formatting

You can do conditional formatting in Excel using VBA. For example, you can highlight a particular cell based on a condition. I have previously submitted a similar post on how to find and highlight duplicate in Excel using VBA. However, there will be situations when you want to highlight an Entire row, based on certain conditions. Here, in this post I’ll share a simple example on highlighting or coloring an entire row using VBA.

1) Change the column width and row height in Excel using VBA: Do you know you can dynamically change the column width or even change the row height in Excel? Yes you can using VBA.

2) Blink or flash multiple cells in Excel using a simple Macro: Blinking cells (with contents) based on certain conditions, can serve as reminders and pull our attention on data that are important to our business. Find out how you can make your wordsheets cells blink using a small macro.

--------------------

The example here, highlights an entire row when the program detects the word cancelled. This was my requirement. It doesn’t matter if the word is in upper or lower case.

The VBA Code

Option Explicit

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
            
            Rows(Target.Row).Interior.Color = vbRed
            Rows(Target.Row).Font.Color = vbWhite
            
        End If
    End If
ErrHandler:
    '
End Sub

The code executes when you type a word in a cell and press the enter key or use the arrow keys to go to the next (or previous) row. This is why I have written my code inside Worksheet_Change event of the sheet. It would check if you or anybody has entered the word cancelled in any cell. See the below image.

Highlight an Entire Row in Excel based on Cell value using VBA

If you want to check the word with a space and take action accordingly, then you can use the method Replace(). For example,

UCase(Replace(Cells(Target.Row, Target.Column), " ", "")) = "CANCELLED"

The Replace() method takes three parameters. The first is the Expression (or the word), the second is the character(s) you want the method to find and the third is the character you wish to replace with.

Highlight Entire Row based on Number Condition

The above examples checks for alpha numeric values. However, sometimes you want to apply conditional formatting on numbers only.

Here's a scenario. I wish to highlight an entire row when a cell has values less than 500. The code would be,

If UsedRange.Rows.Count > 0 Then
    If Trim(Cells(Target.Row, Target.Column)) <> "" And _
        Val(Cells(Target.Row, Target.Column)) <= 500 Then
            
        Rows(Target.Row).Interior.Color = vbRed
        Rows(Target.Row).Font.Color = vbWhite
    End If
End If

← PreviousNext →