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.

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

Saqib Husain

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

Sabealgo

ASKER

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.

Sabealgo

ASKER

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.

Norie

You can use rgbLightBlue, rgbLightYellow, rgbLightGreen and rgbTan.

dlmille

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

Sabealgo

ASKER

Imnorie,

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

?

Norie

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

Sabealgo

ASKER

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.

ASKER CERTIFIED SOLUTION

Norie

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Sabealgo

ASKER

Thank you.