Change row color based on cell value in Excel 2007 VBA

Posted on 2011-10-05
Last Modified: 2012-05-12
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

End With

Open in new window

Question by:printmedia
    LVL 24

    Expert Comment

    Try this

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

    Open in new window

    LVL 24

    Accepted Solution

    Or fractionally shorter:
    For Each cell In MR
        If UCase(cell.Value) = "SHEET" Then cell.Offset(, -9).Resize(, 5).Interior.ColorIndex = 36

    Open in new window


    Author Closing Comment

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

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Join & Write a Comment

    Suggested Solutions

    Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
    Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
    Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    733 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    24 Experts available now in Live!

    Get 1:1 Help Now