Coloring one in between cells with similar names


In one of the columns of my excel file there are cells with similar names. The file is sorted by names of that column. I want cells with similar names to be colored blue for example and the next to be colored grey for example, and again the next to be blue, and the next grey, and so on. If I didn't explain clear enough, please check the example photo.



Selected Answer

Hi Arash and welcome to the Forum.

In the attached file, I've done that with conditional formatting and a "helper column" F (which can be hidden). The good thing about this is that it works even if your rows aren't sorted.

In that column, I put just the value 2 in cell F1 then copied this formula in cell F2 down:

=IF(A1<>A2, -F1,F1)

You'll see that the values are either negative or positive for groups of values in column A.

I then made two "Use a formula.." conditional formatting rules for cell A1. The first to give green format fill is:


Note the less than sign and that there  must be a $ with column name only.

The second to give orange fill is: 


I then went back to Manage Rules and changed the range of "Applies to" from =$A$2 to:

(Take care and check the text when you do that, Excel can add cell names if you move the cursor so it's better to paste the words above). I made it A...D so that all columns of the table are coloured similarly.

Now if you change a value, the colour bands will react. You can change the conditional formatting colurs (via Manage Rules) but remember that if the rules don't apply, any underlying cell colour will be revealed.

You can extend that $D$14 limit but don't forget to copy the helper column down. In fact you could move it to another (hidden) sheet and have the formula copied to 200 rows  say.

Hope this helps.



This is brilliant. Thanks very much. Your parents should be proud. Sorry for my late response
arashsimi (rep: 2) Jul 25, '21 at 11:20 am
Thanks for selecting my Answer, Arash. Glad it helped you. Sadly I'm way too old to have proud parents but it's a nice compliment! 
John_Ru (rep: 1917) Jul 25, '21 at 12:19 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login