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.

Try this conditional format formula,
taken from a post by Tom Ogilvy,
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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Tony" wrote:
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.

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


On Wed, 20 Dec 2006 17:47:00 -0800, Max wrote:

Try this conditional format formula,
taken from a post by Tom Ogilvy,
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

No prob, Gord <g. Cheers
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Gord Dibben wrote:
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

Tony

Select the rows you want with alternating shading.

FormatConditional FormattingFormula is:

=MOD(ROW(),2)=0

Format to a color and OK

Alternating row colrs will stick through filtering.


Gord Dibben MS Excel MVP

On Wed, 20 Dec 2006 15:55:12 -0800, Tony wrote:

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.

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

Gord

On Wed, 20 Dec 2006 17:49:36 -0800, Gord Dibben <gorddibbATshawDOTca wrote:

Tony

Select the rows you want with alternating shading.

FormatConditional FormattingFormula is:

=MOD(ROW(),2)=0

Format to a color and OK

Alternating row colrs will stick through filtering.


Gord Dibben MS Excel MVP

On Wed, 20 Dec 2006 15:55:12 -0800, Tony wrote:

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 MS Excel MVP