Are you already able to colour the whole row based on values in one cell?
You can make this work with absolute column references in your conditional
formatting, and applying it to the whole row.

But if your question is about alternating the colours used I don't know how
this can be done purely with conditional formatting.

You could write a VBA macro. Brute force, perhaps.

Or you could have an additional, hidden column or three to do the work.
E.g.: test if the row is equal to the previous row with =(B1=B2) or
=AND(B1=B2,C1=C2), etc. Then test the true/false transitions (say the first
hidden column is P) e.g. =AND(NOT(P2),P3) - this will return TRUE for the
first row of new groups. Then a final column to do the alternating colours
e.g. =IF(Q2,1-R1,R1) - and seed R1 with 0. Then your conditional formatting
could be ($R1=1) - blue and ($R1=0) - red.

Sounds a bit complicated but if I've understood you right it works. There
would be a problem if some rows were not duplicated.

Joe

> -----Original Message-----
> I'm trying to figure out a way to color band entire rows
> where one cell is identical to the previous row, in MS Excel.
>  For example, say I have a spreadsheet, with:
> Name in column A,
> Street Address in column B,
> City, State and Zip in columns C, D and E.
> Data is sorted by Street Address.
> I would like to be able to band the first set of identical
> addresses (let's say rows 2 & 3) yellow; the second set (rows
> 4, 5 & 6) no color; the third set (rows 7 & 8) are yellow
> again.  There's no limit on the number of rows with the same
> address, but I'm guaranteed to have a minimum of two rows for
> each address.
>
> I have conditional formatting set to change the font color of
> subsequent address cells (B3, B5, B6, B8 in the previous
> example) to white, making it invisible.
> However, I can't figure out how to band the entire row.
>
> Because I'm working with compatibility issues, I would really
> like for the solution to work in Excel 97, 2000 and XP.
>
> Thanks for any help!
> Ron Jobe

                         PCSOFT's List Owner's:
                      Bob Wright<[log in to unmask]>
                       Drew Dunn<[log in to unmask]>