VBA to Highlight entire row with conditions

Abhishekghorpade

Board Regular
Joined
Oct 3, 2018
Messages
71
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,405
Office Version
365
Platform
Windows
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

Board Regular
Joined
Oct 3, 2018
Messages
71
There are multiple workbooks and multiple people using it... so conditional formatting is not helping
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,405
Office Version
365
Platform
Windows

Abhishekghorpade

Board Regular
Joined
Oct 3, 2018
Messages
71

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,405
Office Version
365
Platform
Windows
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

Board Regular
Joined
Oct 3, 2018
Messages
71
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,405
Office Version
365
Platform
Windows
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)?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,405
Office Version
365
Platform
Windows
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
 

Forum statistics

Threads
1,081,421
Messages
5,358,584
Members
400,505
Latest member
JacquiT

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top