General discussion


Color Banding in Excel

By im_chaz ·
I have entries in Excel from A2:B11 that look like this.

Bob 1
Bob 3
Bob 19
Chuck 4
Chuck 5
Trish 4
Tom 6
Tom 6
Dave 40

I would like for each group of names to have their own separate color band. Similar to a Green Bar effect which alternate colors (white and green) but in this case I want it to alternate based on each group of names.

Bob should have 3 rows in one color
Chuck should have 2 rows in another color
Trish should have 1 row in anothercolor
Tom should have 2 rows in another color
Dave should have 1 row in another color

My actual range is quite large. I think their may be a way with conditional formatting but not sure if that will help me due to 3 conditional limitations.
I appreciate any help in advance.


This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

Collapse -

Color Banding in Excel

by Bristar In reply to Color Banding in Excel

First of all, using less technical means will require you to seperate the names from the numbers in individual cells. This will allow you to format based on the entire cell.

However, conditional formatting will allow you to use up to three conditions and will work for this somewhat. For more versatility, you'll need to write a VBA Code that will check the value of the cell that last had focus, compare the criteria (which you can then use the Left() functionality and not split the cells), then select the whole row and change to color. This macro would be run after leaving each cell. It will be slow, but is probably the only way. Let me know if you need help doing this and I can write you some code.

Related Discussions

Related Forums