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?

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?

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?

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?

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?

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?

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?