auto-filter in excel but leave cell background color

[Guest]

I want to create a simple chart with alternating row colors to easily read
data. When I auto-filter the data it moves the cell color. I want to keep
nice alternating rows.

 

[Gord Dibben]

Tony

Select the rows you want with alternating shading.

Format>Conditional Formatting>Formula is:

=MOD(ROW(),2)=0

Format to a color and OK

Alternating row colrs will stick through filtering.


Gord Dibben MS Excel MVP

 

[Gord Dibben]

Max

Thanks for posting.

I thought the =MOD(ROW(),2)=0 would stick through filtering, but my test data
was spurious......who woulda guessed?<g>

Gord

Try this conditional format formula,
taken from a post by Tom Ogilvy, re:
http://tinyurl.com/dsu87

" If the hidden rows are cause by applying a filter then this formula will
work:

=MOD(SUBTOTAL(3,$A$1:A1),2)=0

Select the rows to format (this assumes the formatting starts with row 1)
Enter the formula as if it were for the the currently active cell (in this
case A1). The range must be applied to a column that will contain values in
each visible cell in the column. "

The formula above will also take care of the normal unfiltered situation

If it's without filtering, then we could just use:
=MOD(ROW(),2)=0
as the cond format formula

Gord Dibben MS Excel MVP

 

[Gord Dibben]

Not correct...........please see Max's post for correct.

Gord

Tony

Select the rows you want with alternating shading.

Format>Conditional Formatting>Formula is:

=MOD(ROW(),2)=0

Format to a color and OK

Alternating row colrs will stick through filtering.


Gord Dibben MS Excel MVP

Gord Dibben MS Excel MVP

 

[Max]

No prob, Gord <g>. Cheers