Shading alternate "groups" of rows?

[Lee Harris]

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.

 

[Lee Harris]

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.

PS
the values in the category column are linked via LOOKUP to another sheet,
and this can change dynamically - I will manually sort the data when it does
change though, so I'm guaranteed to have contiguous blocks of data grouped
by category.

 

[Jim Cone]

Give the free Excel add-in "Shade Data Rows" a try.
It can shade by cell value or by every nth row.
Download from ... http://www.realezsites.com/bus/primitivesoftware
No registration required.
--
Jim Cone
San Francisco, USA

"Lee Harris"
<[email protected]>
wrote in message
PS
the values in the category column are linked via LOOKUP to another sheet,
and this can change dynamically - I will manually sort the data when it does
change though, so I'm guaranteed to have contiguous blocks of data grouped
by category.

 

[mama no teeth]

Try this
Assuming your data from A1:B100 Hightlight your data, Conditional Formatting
enter formula then select grey shading

=MOD(--RIGHT($A1,1),2)=1

 

[Lee Harris]

Try this
Assuming your data from A1:B100 Hightlight your data, Conditional
Formatting
enter formula then select grey shading

=MOD(--RIGHT($A1,1),2)=1

how will that work when the right hand character of my categories will be
variable, and generally not a number?

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.

 

[Lee Harris]

Try this
Assuming your data from A1:B100 Hightlight your data, Conditional
Formatting
enter formula then select grey shading

=MOD(--RIGHT($A1,1),2)=1

I think the best way will be to use a lookup table and conditionally format
using that, oh well..

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.

 

[Ron Coderre]

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