|
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. |
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. |
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. |
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. |
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. |
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. |
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. |
| All times are GMT +1. The time now is 02:24 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com