Color alternate rows when after hiding selected rows

[Monk]

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?

 

[Bob Phillips]

=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)

 

[Mike H]

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]

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]

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]

Thanks Mike. Works fine now.

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