Excel - Color code table rows based on value in column A

Can be any visible color (text can be seen too) - but there may be many rows.  I have included sample before and after files and screenshots.
Thank you!!!

K BAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saurabh Singh TeotiaCommented:
Quick question rather then do multi color?? Can we do in cyclic process like

Color-1 Then Color-2 Then Again Color-1 and then Color-2 and so on..

Even after these coloring you will be able to make out and differentiate..
K BAuthor Commented:
Thank you for the reply.
Can we cycle through at least 10 - 20 colors or so? at least 10 maybe?
Saurabh Singh TeotiaCommented:
Can you give me a sheet of excel where you pick 10 color of your choice..I will use them in code rather then picking what i feel as that will save us too and fro on colors part that can we have this color rather then that..

Also just to be clear we are saying 1-10 then 1-10 and then 1-10 and again if you want 20..Give me an excel file with 20 colors..i will use them and then go back to first one...
Introduction to Web Design

Develop a strong foundation and understanding of web design by learning HTML, CSS, and additional tools to help you develop your own website.

K BAuthor Commented:
Column A in the attached spreadsheet.
Thank you again!
K BAuthor Commented:
and yes, repeating is fine.
Saurabh Singh TeotiaCommented:
You gave me entire palette..Thanks for that..However can you send me 10-20 colors that you want to??
K BAuthor Commented:
Apologies.. column A only has 20 now.
Saurabh Singh TeotiaCommented:
You can use the following code to do what you are looking for..

Sub applycolor()
    Dim col As Variant, k As Long
    Dim cl As Variant
    Dim cell As Range, rng As Range, lrow As Long
    k = 0
    col = ("42,41,13,48,7,44,6,4,8,33,54,15,38,40,36,35,34,37,38,2")
    cl = Split(col, ",")
    lrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
    Set rng = Range("A2:A" & lrow)
    For Each cell In rng
        If k > 19 Then k = 0
        If cell.Value <> cell.Offset(1, 0).Value Then
           Range("A" & cell.Row & ":I" & cell.Row).Interior.ColorIndex = cl(k)
            k = k + 1
            Range("A" & cell.Row & ":I" & cell.Row).Interior.ColorIndex = cl(k)
        End If
    Next cell

End Sub

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.