Alternate row shadding even when rows are hidden

[Guest]

Hi,
Can any one help me how to shade the alternate rows even when some rows are
hidden using conditional formats.
forexample.
suppose i am using gray colur for alternate shadding
i hide the rows 2 and 4 now i need the output is like this.
Row 3,6,8 of grey colour.
kindly tell me the solution.

 

[T. Valko]

How are the rows hidden, by using a filter or by using the Hide command?

 

[Guest]

by using hide command only.

How are the rows hidden, by using a filter or by using the Hide command?

 

[T. Valko]

Note that hidding or unhidding rows/columns does not trigger a caclulation
so the rows won't shade properly until a calculation takes place.

If you're using Excel 2003 or later try this:

Assume you want to shade the range A1:C10
Select the range A1:C10
Goto Format>Conditional Formatting
Formula Is: =MOD(SUBTOTAL(103,$A1:$A$2),2)=0
Click the Format button
Select the Patterns tab
Select the desired fill color
OK out

If you're using a version of Excel prior to Excel 2003 then you'll need a
macro to do this. If that's the case I suggest you post in the programming
newsgroup.

 

[Guest]

Hi Valko,
I tried as u said. but i am unable to made the alternate row shading. I am
using Excel 2003. When i select A! to C10 And apply this formula in
Conditional formatting the range A1 to C10 is fully colured but not alternate
rows. Could u please help me out.

 

[T. Valko]

I don't have Excel 2003 to test this.

I have Excel 2002 and that is the same basic formula I'd use to color band a
filtered list. In Excel 2003 they modified the SUBTOTAL function to include
hidden rows. The 103 in the formula means to account for hidden rows.

Try reposting your question in a new thread.

 

[Gord Dibben]

Works as advertised in 2003 version.


Gord Dibben MS Excel MVP

 

[T. Valko]

Thanks for confirming, Gord.

I suspect the OP didn't apply it correctly.