Link to home
Start Free TrialLog in
Avatar of printmedia
printmedia

asked on

Change row color based on cell value in Excel 2007 VBA

Hi all.

I have the code below that changes the color of a cell if it equals "SHEET". But I want to take it to the next level by highlighting the row from Column A to Column E. How can I do this?

Thank you in advance!
Dim rng2 As Long

With Worksheets("Sheet1")

    rng2 = .Range("A" & Rows.Count).End(xlUp).Row
    
 
    .Range("I3:I" & rng2).NumberFormat = "$ 0.0000"
    .Range("D3:D" & rng2).Columns.AutoFit
    .Range("J3:J" & rng2).Columns.AutoFit
    .Range("K3:K" & rng2).Columns.AutoFit
    .Range("L3:L" & rng2).Columns.AutoFit
    
    Set MR = Range("J3:J" & rng2)
    
    For Each cell In MR
        
        If cell.Value = "SHEET" Or cell.Value = "Sheet" Then cell.Interior.ColorIndex = 36
    
    Next

End With

Open in new window

Avatar of StephenJR
StephenJR
Flag of United Kingdom of Great Britain and Northern Ireland image
Try this

For Each cell In MR
        
        If cell.Value = "SHEET" Or cell.Value = "Sheet" Then cell.offset(,-9).resize(,5).Interior.ColorIndex = 36
    
    Next

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of StephenJR
StephenJR
Flag of United Kingdom of Great Britain and Northern Ireland image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of printmedia
printmedia

ASKER

Thanks again Stephen! I'll be posting some more questions.