Try something like this:

Select the rows of your range, with the active cell anywhere in Row_1

From the Excel main menu:
<format<conditional formatting
Condition 1:
Formula is:
=MOD(INT(SUMPRODUCT(($A$1:$A1<"")/COUNTIF($A$1:$A1,$A$1:$A1&""))),2)=1
Click the [Format] button and set the pattern to GREY

Condition 2:
Formula is:
=MOD(INT(SUMPRODUCT(($A$1:$A1<"")/COUNTIF($A$1:$A1,$A$1:$A1&""))),2)=0
Click the [Format] button and set the pattern to WHITE
Click the [OK] buttons

Those formulas count the number of unique items in Col_A and alternate
shading based on whether the count is odd or even.

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Lee Harris" wrote:

I know how to use e.g. MOD functions to conditionally format alternate rows
with shading, but what is the best way to do this if your data is in groups
so that you rather than have alternate rows of grey and white shading, you
might have 5 items in one group that you want grey, then 3 in white, then 7
of grey etc, based on the value in a cell

eg

Column 1, Column 2

[GREY SHADING]category 1, blah...
[GREY SHADING]category 1, blah...
[GREY SHADING]category 1, blah...
[GREY SHADING]category 1, blah...
[WHITE SHADING]category 2, blah...
[WHITE SHADING]category 2, blah...
[GREY SHADING]category 3, blah...
[GREY SHADING]category 3, blah...
[GREY SHADING]category 3, blah...
[GREY SHADING]category 3, blah...
[GREY SHADING]category 3, blah...

so the shading reflects alternation between different categories for ease of
viewing/picking out visually...


I want category 1 rows in light grey, category 2 rows in white, category 3
rows in grey again etc..

the categories will be text, I won't always know what it is either, so I
can't pick out a number from it.


tks in advance for any assistance.