alternate row shading on filtered data
 

For ease in reading a printed spreadsheet with many rows, I want to use
alternate row shading. However, I also use the auto filter to show only
rows that have a value greater than zero in a particular column. That ruins
the alternate row shading. Is there some sort of formula I can use to have
alternate row shading AFTER the filtering?

alternate row shading on filtered data
 

Try this...

Assuming the full unfiltered range A2:C20
A1:C1 = column headers

Select the *entire* range A2:C20 starting from cell A2. Cell A2 will be the
active cell. The active cell is the one cell in the selected range that is
not shaded. The formula will be relative to the active cell.

Goto the menu FormatConditional Formatting
Select the Formula Is option
Enter this formula in the box on the right:
=MOD(SUBTOTAL(3,$A2:$A$20),2)=0
Click the Format button
Select the desired style(s)
OK out

--
Biff
Microsoft Excel MVP


"wallyship" wrote in message
...
For ease in reading a printed spreadsheet with many rows, I want to use
alternate row shading. However, I also use the auto filter to show only
rows that have a value greater than zero in a particular column. That
ruins
the alternate row shading. Is there some sort of formula I can use to
have
alternate row shading AFTER the filtering?

alternate row shading on filtered data
 

Use this formula in Conditional Formatting:
=MOD(SUBTOTAL(3,$A$1:$A2),2)

"wallyship" wrote:

For ease in reading a printed spreadsheet with many rows, I want to use
alternate row shading. However, I also use the auto filter to show only
rows that have a value greater than zero in a particular column. That ruins
the alternate row shading. Is there some sort of formula I can use to have
alternate row shading AFTER the filtering?