Excel 2010: Alternate colors at each change in text value

I want to group like items by color on an Excel 2010 workbook. I need the colors to alternate so I can see where one group ends and the next one begins. I don't see a way to do this with standard conditional formatting.
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:
Have a look over this code here..This is exactly what you are looking for i believe..


PerfishentAuthor Commented:
Any way to do this without VBA?
PerfishentAuthor Commented:
I modified your code so that it alternates between colors. This works, but I was hoping for a solution that didn't require VBA.

    Dim col As Variant, k As Long
    Dim cl As Variant
    Dim clrIdx
    clrIdx = 2
    Dim cell As Range, rng As Range, lrow As Long
    lrow = Cells(Cells.Rows.Count, "F").End(xlUp).row
    Set rng = Range("F2:F" & lrow)
    For Each cell In rng
        If cell.Value <> cell.Offset(1, 0).Value Then
           Range("F" & cell.row & ":F" & cell.row).Interior.ColorIndex = clrIdx
            If clrIdx = 2 Then
                clrIdx = 33
                clrIdx = 2
            End If
            Range("F" & cell.row & ":F" & cell.row).Interior.ColorIndex = clrIdx
        End If
    Next cell
Exploring SharePoint 2016

Explore SharePoint 2016, the web-based, collaborative platform that integrates with Microsoft Office to provide intranets, secure document management, and collaboration so you can develop your online and offline capabilities.

ProfessorJimJamMicrosoft Excel ExpertCommented:
You can do this with conditional formatting and to learn how to do it. See the article written in EE by Gleen
barry houdiniCommented:
I searched for articles by Gleen but I couldn't find any - is that the right name Professor? Do you have a link?

I used conditional formatting on the attached to format all the rows in my table based on how the text is grouped in column A.

The formulas I used in conditional formatting were as follows:


Try changing the text in column A to see how the formatting changes too

I think you can make that work for your data - post back if you have any queries

regards, barry
ProfessorJimJamMicrosoft Excel ExpertCommented:
thank you barry.   i liked your solution, from my point of view it is much better than the one in the article i referred.

my mistake,  the Name was Glenn and here is the link for the Article http://www.experts-exchange.com/articles/13740/Excel-Conditional-Formatting-The-Better-To-See-You-With.html

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
Rob HensonFinance AnalystCommented:
I have achieved this in the past with a helper column with this formula in column A:


 This alternates the value in column A between 1 and 0 based on the change of reference in column D. Then use conditional format to add a colour for the lines equal to 1.
PerfishentAuthor Commented:
Saurabh's VBA solution works, but the simpler formula-based solutions offered by ProfessorJimJam and Rob Henson are what I was looking for.
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.