Alternate row shading with a filtered spreadsheet

  • Thread starter Thread starter glenlee
  • Start date Start date
G

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.
 
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
 
Unfortunately, its a no go. Entering the Subtotal formula returns the
#VALUE! error. I'm using Excel 2002.
 
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.
 
=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
 
Back
Top