Alternate row shadding even when rows are hidden
 

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.

Alternate row shadding even when rows are hidden
 

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


--
Biff
Microsoft Excel MVP


"Radhakrishna k.v.n.r" <Radhakrishna
wrote in message ...
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.

Alternate row shadding even when rows are hidden
 

by using hide command only.

"T. Valko" wrote:

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


--
Biff
Microsoft Excel MVP


"Radhakrishna k.v.n.r" <Radhakrishna
wrote in message ...
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.

Alternate row shadding even when rows are hidden
 

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 FormatConditional 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.



--
Biff
Microsoft Excel MVP


"Radhakrishna k.v.n.r" wrote in
message ...
by using hide command only.

"T. Valko" wrote:

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


--
Biff
Microsoft Excel MVP


"Radhakrishna k.v.n.r" <Radhakrishna
wrote in message
...
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.

Alternate row shadding even when rows are hidden
 

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" wrote:

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 FormatConditional 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.



--
Biff
Microsoft Excel MVP


"Radhakrishna k.v.n.r" wrote in
message ...
by using hide command only.

"T. Valko" wrote:

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


--
Biff
Microsoft Excel MVP


"Radhakrishna k.v.n.r" <Radhakrishna
wrote in message
...
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.

Alternate row shadding even when rows are hidden
 

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.

--
Biff
Microsoft Excel MVP


"Radhakrishna k.v.n.r" wrote in
message ...
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" wrote:

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 FormatConditional 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.



--
Biff
Microsoft Excel MVP


"Radhakrishna k.v.n.r" wrote
in
message ...
by using hide command only.

"T. Valko" wrote:

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


--
Biff
Microsoft Excel MVP


"Radhakrishna k.v.n.r" <Radhakrishna

wrote in message
...
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.

Alternate row shadding even when rows are hidden
 

Works as advertised in 2003 version.


Gord Dibben MS Excel MVP

On Mon, 15 Oct 2007 14:34:09 -0400, "T. Valko" wrote:

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.

Alternate row shadding even when rows are hidden
 

Thanks for confirming, Gord.

I suspect the OP didn't apply it correctly.

--
Biff
Microsoft Excel MVP


"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
Works as advertised in 2003 version.


Gord Dibben MS Excel MVP

On Mon, 15 Oct 2007 14:34:09 -0400, "T. Valko"
wrote:

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.