| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Shading alternate "groups" of rows?
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Shading alternate "groups" of rows?
"Lee Harris" wrote in message ... 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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Shading alternate "groups" of rows?
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" 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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Shading alternate "groups" of rows?
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" 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. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Shading alternate "groups" of rows?
"mama no teeth" wrote in message ... 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? "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. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Shading alternate "groups" of rows?
"mama no teeth" wrote in message ... 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.. "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. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Shading alternate "groups" of rows?
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Alternate shading colors | Excel Discussion (Misc queries) | |||
| Hiding rows while sheet is protected | Excel Discussion (Misc queries) | |||
| Summing Alternate Rows | Excel Discussion (Misc queries) | |||
| HOW CAN I AUTOMATICALLY INSERT ALTERNATE BLANK ROWS IN A LIST? | Excel Discussion (Misc queries) | |||
| How do I shade alternate rows in Excel 2002? | Excel Discussion (Misc queries) | |||
Hybrid Mode
