Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Color alternate rows when after hiding selected rows
Hi
I can color alternate rows with the mod(row();2)=0 condition however my worksheet also incorporates the use of hiding rows where a column value is blank. This has the effect of distorting the colored rows. I understand there is a condition you can use for filtering but I don't want to go down that path. Is there a formatting conditon or vba code that will give me the alternate row colors after hiding the rows? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Color alternate rows when after hiding selected rows
=MOD(SUBTOTAL(3,$A1:$A$2),2)=0
-- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Monk" wrote in message ... Hi I can color alternate rows with the mod(row();2)=0 condition however my worksheet also incorporates the use of hiding rows where a column value is blank. This has the effect of distorting the colored rows. I understand there is a condition you can use for filtering but I don't want to go down that path. Is there a formatting conditon or vba code that will give me the alternate row colors after hiding the rows? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Color alternate rows when after hiding selected rows
Hi,
Donig it on a filtered range is a variation on the formula you are using. Select your range and use this =MOD(SUBTOTAL(3,$A$1:$A2),2) Apply the filter and alternate rows should be coloured. Change A1 A2 to the top of your range. Mike "Monk" wrote: Hi I can color alternate rows with the mod(row();2)=0 condition however my worksheet also incorporates the use of hiding rows where a column value is blank. This has the effect of distorting the colored rows. I understand there is a condition you can use for filtering but I don't want to go down that path. Is there a formatting conditon or vba code that will give me the alternate row colors after hiding the rows? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Color alternate rows when after hiding selected rows
Thanks Mike, I am still experiencing difficulty as it doesn't change when I
hide the rows (i.e there can be three colored rows together). If my range to format is a11:M512 how would the formula below be structured? Thanks in advance. "Mike H" wrote: Hi, Donig it on a filtered range is a variation on the formula you are using. Select your range and use this =MOD(SUBTOTAL(3,$A$1:$A2),2) Apply the filter and alternate rows should be coloured. Change A1 A2 to the top of your range. Mike "Monk" wrote: Hi I can color alternate rows with the mod(row();2)=0 condition however my worksheet also incorporates the use of hiding rows where a column value is blank. This has the effect of distorting the colored rows. I understand there is a condition you can use for filtering but I don't want to go down that path. Is there a formatting conditon or vba code that will give me the alternate row colors after hiding the rows? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Color alternate rows when after hiding selected rows
Hi,
Select your range of cells A11 - M512 then Format|Conditional format Formula is =MOD(SUBTOTAL(3,$A$10:$A11),2) Select a colour Note that you have row 11 selected and the formula start in A10. Always start 1 row above the selected range. Mike "Monk" wrote: Thanks Mike, I am still experiencing difficulty as it doesn't change when I hide the rows (i.e there can be three colored rows together). If my range to format is a11:M512 how would the formula below be structured? Thanks in advance. "Mike H" wrote: Hi, Donig it on a filtered range is a variation on the formula you are using. Select your range and use this =MOD(SUBTOTAL(3,$A$1:$A2),2) Apply the filter and alternate rows should be coloured. Change A1 A2 to the top of your range. Mike "Monk" wrote: Hi I can color alternate rows with the mod(row();2)=0 condition however my worksheet also incorporates the use of hiding rows where a column value is blank. This has the effect of distorting the colored rows. I understand there is a condition you can use for filtering but I don't want to go down that path. Is there a formatting conditon or vba code that will give me the alternate row colors after hiding the rows? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Color alternate rows when after hiding selected rows
Thanks Mike. Works fine now.
"Mike H" wrote: Hi, Select your range of cells A11 - M512 then Format|Conditional format Formula is =MOD(SUBTOTAL(3,$A$10:$A11),2) Select a colour Note that you have row 11 selected and the formula start in A10. Always start 1 row above the selected range. Mike "Monk" wrote: Thanks Mike, I am still experiencing difficulty as it doesn't change when I hide the rows (i.e there can be three colored rows together). If my range to format is a11:M512 how would the formula below be structured? Thanks in advance. "Mike H" wrote: Hi, Donig it on a filtered range is a variation on the formula you are using. Select your range and use this =MOD(SUBTOTAL(3,$A$1:$A2),2) Apply the filter and alternate rows should be coloured. Change A1 A2 to the top of your range. Mike "Monk" wrote: Hi I can color alternate rows with the mod(row();2)=0 condition however my worksheet also incorporates the use of hiding rows where a column value is blank. This has the effect of distorting the colored rows. I understand there is a condition you can use for filtering but I don't want to go down that path. Is there a formatting conditon or vba code that will give me the alternate row colors after hiding the rows? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Color alternate rows when after hiding selected rows
Hi Mike, Sorry I am still having difficulty here. Got it working the other
night but cannot replicate it now. I have conditionally formated the range below, but is any formula entered in the header row 10. If I hide rows based on having ) ina certain column the alternating color does not work. It retains its original formatting which can lead to blocks of color and blocks of white. Sorry but can you please assist as to what I am doing wrong? "Mike H" wrote: Hi, Select your range of cells A11 - M512 then Format|Conditional format Formula is =MOD(SUBTOTAL(3,$A$10:$A11),2) Select a colour Note that you have row 11 selected and the formula start in A10. Always start 1 row above the selected range. Mike "Monk" wrote: Thanks Mike, I am still experiencing difficulty as it doesn't change when I hide the rows (i.e there can be three colored rows together). If my range to format is a11:M512 how would the formula below be structured? Thanks in advance. "Mike H" wrote: Hi, Donig it on a filtered range is a variation on the formula you are using. Select your range and use this =MOD(SUBTOTAL(3,$A$1:$A2),2) Apply the filter and alternate rows should be coloured. Change A1 A2 to the top of your range. Mike "Monk" wrote: Hi I can color alternate rows with the mod(row();2)=0 condition however my worksheet also incorporates the use of hiding rows where a column value is blank. This has the effect of distorting the colored rows. I understand there is a condition you can use for filtering but I don't want to go down that path. Is there a formatting conditon or vba code that will give me the alternate row colors after hiding the rows? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Color Alternate Rows | Excel Worksheet Functions | |||
Hiding Specific Rows Based on Values in Other Rows | Excel Worksheet Functions | |||
Hiding Rows if the linked rows are blank | Excel Discussion (Misc queries) | |||
Add alternate rows | Excel Worksheet Functions | |||
How to sum-up alternate rows ? | Excel Worksheet Functions |