Shading alternate "groups" of rows?

L

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.
 
L

Lee Harris

Lee Harris said:
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.
Click to expand...

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.
 
J

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.
 
G

Guest

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
 
G

Guest

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
 
L

Lee Harris

mama no teeth said:
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
Click to expand...

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


Lee Harris said:
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.
Click to expand...
Click to expand...
 
L

Lee Harris

mama no teeth said:
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
Click to expand...

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


Lee Harris said:
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.
Click to expand...
Click to expand...
 
L

Lee Harris

mama no teeth said:
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
Click to expand...

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

Lee Harris said:
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.
Click to expand...
Click to expand...
 
L

Lee Harris

mama no teeth said:
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
Click to expand...

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

Lee Harris said:
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.
Click to expand...
Click to expand...
 
G

Guest

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
 
G

Guest

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Cell Shading 2
Alternate Row Shading (Visible Rows Only) 9
Comparison of columns using Conditional Formatting 3
Conditional Formatting using blocks of rows 7
Alternate Column Shading to be applied only to visible columns 6
sort columns, but not shading 3
Apply Alternate Shading to selected rows 2
alternate row shading on filtered data 2

Top