Excel 2007 : change cell colour based on grouped values

Hi guys,

I'm new to this forum and hope I won't be disappointed :-)

So to start with I have a spreadsheet with a few columns.

I have grouped by one of the columns...which looks something like this:

a
a
a
a
b
b
b
b
c
c
c
d
d
e
f

Anyway, what I want to do is to have a different colour everytime the value changes (like i have shows above)...so all a's will be one colour, b's will be another, c's another etc...it will let me see easier where we only have 1, 2 or 3 rows (I expect to see 4). I don't mind if the colour alternates between two colours e.g Red and Yellow.

I can't find what I'm looking for in conditional formatting (maybe I'm not looking hard enough!).

Please help!

Thanks

ps - I am using Excel 2007

Assuming the data is in A2:A20 then select the range and go to Home|Conditional Formatting|New Rule.

Select "use a formula to determine which cells to format" and enter formula like:

=MOD(ROUNDUP(SUM(1/COUNTIF($A$2:$A2,$A$2:$A2)),0),2)=1

click Format and choose a colour.

This will alternate that colour and the default white....

If want a colour other than white, you can add a conditional format with another similar formula

=MOD(ROUNDUP(SUM(1/COUNTIF($A$2:$A2,$A$2:$A2)),0),2)=0

and pick another colour...

You are a DON!!!

Thanks ever so much...works perfectly! :-)

You are welcome.

If you are satisfied with the solution provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save