VBA to Highlight entire row with conditions

Abhishekghorpade

Board Regular
Joined
Oct 3, 2018
Messages
78
Hi,
I am looking for VBA to highlight the entire rows which satisfy any one of the following conditions for active sheet.

1. IF Column J blank and Column R has some data then Highlight(vbyellow)
2. If Column P and Column R both are ‘0’ and column J is ‘A’ then highlight (vbyellow)
3. Either Column S or T has ‘0’ (Both has value ignore and both has 0 ignore) highlight (vbyellow)
4. If column H is less than 2018 and has value in Column R & P then highlight (vbyellow)
5. If column B has duplicate that is same Name and Column E and F also matches then Highlight
 
Try this:
Code:
Sub MyMacro()

    Dim lr As Long
    Dim r As Long
    Dim hl As Boolean
    
    Application.ScreenUpdating = False
    
'   ***Uncomment the line below if you want it to remove current highlighting before starting***
    'Cells.Interior.Pattern = xlNone
    
'   Find the last row with data (pick a column that will always have data, I chose "A" for this example)
    lr = Cells(Rows.Count, "A").End(xlUp).Row
    
'   Loop through all rows of data starting on row 2 (assuming row 1 has headers)
    For r = 2 To lr
'       Initialize highlight variable
        hl = False
'       Check first condition
        If Cells(r, "J") = "" And Cells(r, "R") <> "" Then
            hl = True
'       Check second condition
        ElseIf Cells(r, "J") = "A" And Cells(r, "P") = 0 And Cells(r, "R") = 0 Then
            hl = True
'       Check third condition
        ElseIf Cells(r, "S") = 0 Or Cells(r, "T") = 0 Then
            hl = True
'       Check fourth condition
        ElseIf Cells(r, "H") < 2018 And Cells(r, "P") <> 0 And Cells(r, "R") <> 0 Then
            hl = True
'       Check fifth condition
        ElseIf Cells(r, "B") = Cells(r, "E") And Cells(r, "B") = Cells(r, "F") Then
            hl = True
        End If
'       Highlight row dependent on varaible
        If hl Then Rows(r).Interior.Color = 65535
    Next r
    
    Application.ScreenUpdating = True
    
End Sub
Note the following assumptions:
1. I am assuming that row 1 is a header and the data starts on row 2
2. I am assuming that every row that has data will have column A populated
If either of those are not true, we will need to make minor modifications to the code.

Also, if you want it to re-evaluate when run, and remove all existing highlighting, uncomment the line at the top of the code by removing the single quote mark (') in front of:
Code:
Cells.Interior.Pattern = xlNone

Condition 3: is not working properly.. If Column S and T both are 0 it should not highlight the row..
 
Upvote 0

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Condition 3: is not working properly.. If Column S and T both are 0 it should not highlight the row..
In your original condition, you said "Either Column S or T has ‘0’", which implies only one has to be 0 for it to be met, hence the "or" condition.
Now it looks like you saying if BOTH are zero (which is not an "or", but rather an "and").
So if that is what you really want, change the word "or" in the third condition to "and".
 
Upvote 0
Try this:
Code:
Sub MyMacro()

    Dim lr As Long
    Dim r As Long
    Dim hl As Boolean
    
    Application.ScreenUpdating = False
    
'   ***Uncomment the line below if you want it to remove current highlighting before starting***
    'Cells.Interior.Pattern = xlNone
    
'   Find the last row with data (pick a column that will always have data, I chose "A" for this example)
    lr = Cells(Rows.Count, "A").End(xlUp).Row
    
'   Loop through all rows of data starting on row 2 (assuming row 1 has headers)
    For r = 2 To lr
'       Initialize highlight variable
        hl = False
'       Check first condition
        If Cells(r, "J") = "" And Cells(r, "R") <> "" Then
            hl = True
'       Check second condition
        ElseIf Cells(r, "J") = "A" And Cells(r, "P") = 0 And Cells(r, "R") = 0 Then
            hl = True
'       Check third condition
        ElseIf Cells(r, "S") = 0 Or Cells(r, "T") = 0 Then
            hl = True
'       Check fourth condition
        ElseIf Cells(r, "H") < 2018 And Cells(r, "P") <> 0 And Cells(r, "R") <> 0 Then
            hl = True
'       Check fifth condition
        ElseIf Cells(r, "B") = Cells(r, "E") And Cells(r, "B") = Cells(r, "F") Then
            hl = True
        End If
'       Highlight row dependent on varaible
        If hl Then Rows(r).Interior.Color = 65535
    Next r
    
    Application.ScreenUpdating = True
    
End Sub
Note the following assumptions:
1. I am assuming that row 1 is a header and the data starts on row 2
2. I am assuming that every row that has data will have column A populated
If either of those are not true, we will need to make minor modifications to the code.

Also, if you want it to re-evaluate when run, and remove all existing highlighting, uncomment the line at the top of the code by removing the single quote mark (') in front of:
Code:
Cells.Interior.Pattern = xlNone

Instead of Highlighting the entire row is there a way to highlight the particular cells
EG: IF Column J blank and Column R has some data then Highlight(vbyellow) only J
 
Upvote 0
Instead of Highlighting the entire row is there a way to highlight the particular cells
EG: IF Column J blank and Column R has some data then Highlight(vbyellow) only J
You would need to remove these two sections:
Code:
'       Initialize highlight variable
        hl = False
Code:
'       Highlight row dependent on varaible
        If hl Then Rows(r).Interior.Color = 65535

Then, replace all the instance of "hl = True" with whatever you want to highlight in that particular instance.

For example, your first one would look like this:
Code:
'       Check first condition
        If Cells(r, "J") = "" And Cells(r, "R") <> "" Then
[B][COLOR=#ff0000]            Cells(r, "J").Interior.Color = 65535[/COLOR][/B]
You should be able to do the rest, it all follows the same pattern. The letter between quotes is the column you want highlighted.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,255
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top