Excel - Color coding a row or rows based on the value of one cell.

Sabealgo
Sabealgo used Ask the Experts™
on
How do I write a macro that looks at column D and highlights the entire row based on that value?  The macro needs to look at the values of a certain cell and based on that value change the entire row to one color or another.

For example, I work on a list of quote requests.  The list contains requests from four different vendors.  Because the data on each row of the list is so similar, I would like to color code the row based on the vendor's name so that the line can be easily identified.

Specifically, Row D contains the vendors name. - The data on that row, extends from column A through BA.  A sample spreadsheet is attached.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
NorieAnalyst Assistant

Commented:
Do you mean column D?

How would you like to colour code each row?

This would colour the rows with VendorA in column D green, with VendorB red and VendorC yellow.

Sub ColourVendors()
Dim rng As Range
Dim cl As Range
Dim LastRow As Long
Dim idxColour As Long

    LastRow = Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row

    Set rng = Worksheets("Sheet1").Range("A2:BA" & LastRow)

    For Each cl In rng.Columns(4).Cells

        Select Case cl.Value
            Case "VendorA"
                idxColour = vbGreen
            Case "VendorB"
                idxColour = vbRed
            Case "VendorC"
                idxColour = vbYellow
        End Select

        Intersect(cl.EntireRow, rng).Interior.Color = idxColour
    Next cl
End Sub

Open in new window

Sample file not found. Does this have to be a macro or can you also use conditional formatting?

Author

Commented:
I would like it to be a macro so that I can color the entire row line - not just the cell with the matching name.  I will try the first solution and see if that works.
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Author

Commented:
Hello Imnorie,
Can you provide me with the color for light blue, light yellow, light green and tan?  As if I were to take a highlighter pen and highlight each line.  

Thanks.
NorieAnalyst Assistant

Commented:
You can use rgbLightBlue, rgbLightYellow, rgbLightGreen and rgbTan.
Most Valuable Expert 2012
Top Expert 2012

Commented:
With conditional formatting, you can color the entire row line based on a match in one cell

Author

Commented:
Imnorie,



So then it would be ?
        Select Case cl.Value
            Case "VendorA"
                idxcolor = rgbLightBlue

?
NorieAnalyst Assistant

Commented:
Yes, if you wanted VendorA rows to be coloured light blue.

Author

Commented:
When I plug in the idxcolor - rgbLightBlue all of the lines associated with that vendor are coded black.  Similarly, when I used the other rgb colors, they came up coded as black as well.
Analyst Assistant
Commented:
Here's a change that won't format any rows that don't match the names in the code.
Sub ColourVendors()
Dim rng As Range
Dim cl As Range
Dim LastRow As Long
Dim idxColour As Long

    LastRow = Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row

    Set rng = Worksheets("Sheet1").Range("A2:BA" & LastRow)

    For Each cl In rng.Columns(4).Cells

        Select Case cl.Value
            Case "VendorA"
                idxColour = rgbLightGreen
            Case "VendorB"
                idxColour = rgbLightYellow
            Case "VendorC"
                idxColour = rgbMaroon
            Case Else
                idxColour = xlNone
        End Select

        Intersect(cl.EntireRow, rng).Interior.Color = idxColour
    Next cl
End Sub

Open in new window

This won't fix the problem, just stop the row being coloured black.

To fix the problem you need to check that the vendor names you are using in the code match those on the worksheet, including the case.

Author

Commented:
Thank you.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial