Is it possible to have every other row shaded in a filtered list? I'd
like to keep the alternate shading, no matter what the filter is set
to.

Hi Glen,
Sure Chip Pearson has a page on
Color Banding with Conditional Formattin
http://www.cpearson.com/excel/banding.htm

and I have a page on Conditional Formatting
http://www.mvps.org/dmcritchie/excel/condfmt.htm

---
HTH,
David McRitchie, Microsoft MVP - Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"glenlee" wrote in message ...
Is it possible to have every other row shaded in a filtered list? I'd
like to keep the alternate shading, no matter what the filter is set
to.

Yes. First off, you'll need one column of data in your filtered range that
does not contain any blanks. Second, you'll need to add a "helper" column
and fill it with this formula:

=SUBTOTAL(103,$A$2:A2)

This assumes that Column A starts at A2 (A1 being your header) and does not
contain any blank values.

Now, apply Conditional Formatting using this custom formula.

=MOD($D2,2)=0

This assumes that Column D is your newly inserted "helper" column and again,
starts at D2.

Set your formatting, and voila, the alternate shading should be applied
correctly regarless of your filter.

(Note: if you have an older version of Excel, this may not work, as the
SUBTOTAL function was changed in more recent versions.)

HTH,
Elkar


"glenlee" wrote:

Is it possible to have every other row shaded in a filtered list? I'd
like to keep the alternate shading, no matter what the filter is set
to.

Unfortunately, its a no go. Entering the Subtotal formula returns the
#VALUE! error. I'm using Excel 2002.


On Fri, 8 Dec 2006 17:09:00 -0800, ?B?RWxrYXI=?=
wrote:

Yes. First off, you'll need one column of data in your filtered range that
does not contain any blanks. Second, you'll need to add a "helper" column
and fill it with this formula:

=SUBTOTAL(103,$A$2:A2)

This assumes that Column A starts at A2 (A1 being your header) and does not
contain any blank values.

Now, apply Conditional Formatting using this custom formula.

=MOD($D2,2)=0

This assumes that Column D is your newly inserted "helper" column and again,
starts at D2.

Set your formatting, and voila, the alternate shading should be applied
correctly regarless of your filter.

(Note: if you have an older version of Excel, this may not work, as the
SUBTOTAL function was changed in more recent versions.)

HTH,
Elkar


"glenlee" wrote:

Is it possible to have every other row shaded in a filtered list? I'd
like to keep the alternate shading, no matter what the filter is set
to.

Thanks, got it to work using your page instructions with this
conditional formatting formula:
=MOD(SUBTOTAL(3,$A$1:$A1),2)
Formats every other row, regardless of what the filter is set on.

On Fri, 8 Dec 2006 19:59:55 -0500, "David McRitchie"
wrote:

Hi Glen,
Sure Chip Pearson has a page on
Color Banding with Conditional Formattin
http://www.cpearson.com/excel/banding.htm

and I have a page on Conditional Formatting
http://www.mvps.org/dmcritchie/excel/condfmt.htm

---
HTH,
David McRitchie, Microsoft MVP - Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"glenlee" wrote in message ...
Is it possible to have every other row shaded in a filtered list? I'd
like to keep the alternate shading, no matter what the filter is set
to.

=SUBTOTAL(103,$A$2:A2)

In Excel 2002 the formula would be:

=SUBTOTAL(3,$A$2:A2)

The 100 series arguments were added in Excel 2003 and up. They are used for
hidden rows.

Biff

"glenlee" wrote in message
...
Unfortunately, its a no go. Entering the Subtotal formula returns the
#VALUE! error. I'm using Excel 2002.


On Fri, 8 Dec 2006 17:09:00 -0800, ?B?RWxrYXI=?=
wrote:

Yes. First off, you'll need one column of data in your filtered range
that
does not contain any blanks. Second, you'll need to add a "helper" column
and fill it with this formula:

=SUBTOTAL(103,$A$2:A2)

This assumes that Column A starts at A2 (A1 being your header) and does
not
contain any blank values.

Now, apply Conditional Formatting using this custom formula.

=MOD($D2,2)=0

This assumes that Column D is your newly inserted "helper" column and
again,
starts at D2.

Set your formatting, and voila, the alternate shading should be applied
correctly regarless of your filter.

(Note: if you have an older version of Excel, this may not work, as the
SUBTOTAL function was changed in more recent versions.)

HTH,
Elkar


"glenlee" wrote:

Is it possible to have every other row shaded in a filtered list? I'd
like to keep the alternate shading, no matter what the filter is set
to.