Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 65
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 10,593
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 11,501
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 65
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 11,501
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 65
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 65
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Color Alternate Rows Max Excel Worksheet Functions 0 April 1st 08 12:41 AM
Hiding Specific Rows Based on Values in Other Rows Chris Excel Worksheet Functions 1 November 2nd 06 09:21 PM
Hiding Rows if the linked rows are blank KG Excel Discussion (Misc queries) 9 May 18th 05 02:32 AM
Add alternate rows Teri Excel Worksheet Functions 6 March 16th 05 11:15 PM
How to sum-up alternate rows ? lolex Excel Worksheet Functions 3 November 5th 04 12:57 PM


All times are GMT +1. The time now is 11:45 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"