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

 

[Joe4]

Why not use Conditional Formatting instead of VBA?
The nice thing about that is it is dynamic, and requries no VBA (no sense in "recreating the wheel", if that will suffice).

 

[Abhishekghorpade]

There are multiple workbooks and multiple people using it... so conditional formatting is not helping

 

[Joe4]

There are multiple workbooks and multiple people using it... so conditional formatting is not helping

That should not make any difference.
Conditional formatting is not dependent upon user or their copy of Excel.

Perhaps this is the issue you are referring to: https://www.mrexcel.com/forum/excel-questions/585297-conditional-formatting-shared-workbook.html
If so, it is explained how to add that in.

 

[Abhishekghorpade]

That should not make any difference.
Conditional formatting is not dependent upon user or their copy of Excel.

Perhaps this is the issue you are referring to: https://www.mrexcel.com/forum/excel-questions/585297-conditional-formatting-shared-workbook.html
If so, it is explained how to add that in.

Thank you so much for your time.. But i am looking VBA for this.. I have other plans for that coding too

 

[Joe4]

Note that you CAN program VBA to create Conditional Formatting (CF).
You can simply turn on the Macro Recorder and record yourself entering the CF to get VBA code to do this.

If you want to avoid CF altogether, and use strictly VBA code, how you do it will depend on a few different things:
- Are you doing this on existing data, or do you want this to run automatically on new data being entered?
- If on existing data, you would just create a normal VBA procedure. If you do not want to use CF, you will probably need to loop through each row to do each check one row at a time. Note that this is MUCH less efficient than using CF, and could be slow.
- If you want it to run on new data as it is manually entered into the system, you would need to use Worksheet_Change event procedure code that will automatically be triggered upon new data entry.

 

[Abhishekghorpade]

Note that you CAN program VBA to create Conditional Formatting (CF).
You can simply turn on the Macro Recorder and record yourself entering the CF to get VBA code to do this.

If you want to avoid CF altogether, and use strictly VBA code, how you do it will depend on a few different things:
- Are you doing this on existing data, or do you want this to run automatically on new data being entered?
- If on existing data, you would just create a normal VBA procedure. If you do not want to use CF, you will probably need to loop through each row to do each check one row at a time. Note that this is MUCH less efficient than using CF, and could be slow.
- If you want it to run on new data as it is manually entered into the system, you would need to use Worksheet_Change event procedure code that will automatically be triggered upon new data entry.

It will be on existing data. And i would like to exclude CF

 

[Joe4]

Are blanks and 0 to be created the same?
For example, with Condition 3:

3. Either Column S or T has ‘0’ (Both has value ignore and both has 0 ignore) highlight (vbyellow)

What if either S or T has no entry (blank)?

 

[Abhishekghorpade]

Are blanks and 0 to be created the same?
For example, with Condition 3:

What if either S or T has no entry (blank)?

Yes, Blank and 0 to be created same

 

[Joe4]

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