Colour coding blocks of rows using conditional formatting

My macros have been taken away; never to be used again! I'm gutted. I now need to use formatting to help me highlight the different users and their data. Doing this manually is a chore.

Using Excel 2007, is there any way to say 'alternate colour between Grey and White based on the matching names in row A'? Row A is sorted so that names are in order and together.

Bob (White)
Bob (White)
Derek (Grey)
Jane (White)
Jane (White)
Jane (White)
Jane (White)
etc (Grey)
etc (etc)

Edit: completely revised my answer. This one required a "helper column" but seems to work much better.

From David McRitchie's Excel Pages site on conditional formatting:

We want to Group anytime there is a Change in Column A or Column B. Conditional Formatting can not keep track of what the previous color was, so we will have to use a helper column, and then have Conditional Formatting check the helper column.

The basic formula for the required helper column is:

E1:  0 
E2: =MOD(OFFSET($E2,-1,0)+ OR($A2<>OFFSET($A2,-1,0),$B2<>OFFSET($B2,-1,0)),2)

From E2, just drag the formula down as far as you need to with your data.

Your conditional formatting formula will be:

=$E1=1 

Visit the conditional formatting in groups page for an explanation of how and why this works.

Assuming your data start on row 2 (i.e. row 1 is a header) and column A is the 'data group' headings (your Bob's and Dereks above), try this:

Set up an additional column (which you'll hide later) and put this formula in row 2:

=IF(I1=1,(IF(A2=A1,1,0)),(IF(A2=A1,0,1)))

Drag this formula all the way to the bottom of your data set. You should now have a tidy set of 1's and 0's which match your data blocks. (1's for first block, 0's for second block, 1's for third block and so on)

Now setup a simple conditional format against row I:

• Formula: =$I5=1
• Format: set format to gray
• Applies to =$A$2:$G$100

As long as you keep row I outside of the autofilter selection, it'll even work dynamically as you sort your data (using autofilter).

If you want to do this without a helper column, you can use this as your CF formula:

=MOD(SUMPRODUCT(($A$2:$A2<>$A$1:$A1)*1),2)=0

Just select your formatting, and you're done.