VBA to Highlight entire row with conditions

[Abhishekghorpade]

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

 

[Abhishekghorpade]

Try this:

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:

Cells.Interior.Pattern = xlNone

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

 

[Joe4]

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".

 

[Abhishekghorpade]

Try this:

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:

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

 

[Joe4]

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:

'       Initialize highlight variable
        hl = False

'       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:

'       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.