Link to home
Start Free TrialLog in
Avatar of Sabealgo

asked on

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

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.
Avatar of Norie

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?
Avatar of Sabealgo


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.
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.  

You can use rgbLightBlue, rgbLightYellow, rgbLightGreen and rgbTan.
With conditional formatting, you can color the entire row line based on a match in one cell

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

Yes, if you wanted VendorA rows to be coloured light blue.
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.
Avatar of Norie

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thank you.