Alternate row shading with a filtered spreadsheet

[glenlee]

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.

 

[David McRitchie]

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

 

[Guest]

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]

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

 

[glenlee]

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.

 

[T. Valko]

=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