auto-filter in excel but leave cell background color

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
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
 
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
Click to expand...

Gord Dibben MS Excel MVP
 
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
Click to expand...

Gord Dibben MS Excel MVP
 
Back
Top