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

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.

The example here, highlights an entire row when the program detects the word cancelled. This is 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 would want to apply conditional formatting on numbers too.

Here’s a scenario. I wish to highlight an entire row when a cell has values less than 500.

The VBA Code
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

Well, that’s it. Thanks for reading.

Like this Article? Subscribe now, and get all the latest articles and tips, right in your inbox.

Enter your email id

Delivered by FeedBurner
Tweet this article Facebook Google+

Related Posts:

comments powered by Disqus

Join our Google Plus Community and be a part of a discussion!