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.
Microsoft ApplicationsMicrosoft OfficeMicrosoft Excel

Avatar of undefined
Last Comment

8/22/2022 - Mon

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?

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.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes

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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.


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.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question

Thank you.