I use =MOD(ROW(),2)=1 to shade every other row in my worksheet.

When auto filter is used it will not keep every other row shaded. Often
shaded rows are together.

Is there a more flexible way so that auto filter will not affect the
shading. It looks strange when a sheet is printed.

Hank

i think gord posted this

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


--


Gary


"HH" wrote in message
...
I use =MOD(ROW(),2)=1 to shade every other row in my worksheet.

When auto filter is used it will not keep every other row shaded. Often
shaded rows are together.

Is there a more flexible way so that auto filter will not affect the shading.
It looks strange when a sheet is printed.

Hank

Thanks Gary,

I replaced your code with the one I had in conditional formating. Unless
I missed something, that code shades the entire worksheet. I'm trying keep
every other row shaded - with or without auto filter being used.

Hank
"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
i think gord posted this

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


--


Gary


"HH" wrote in message
...
I use =MOD(ROW(),2)=1 to shade every other row in my worksheet.

When auto filter is used it will not keep every other row shaded. Often
shaded rows are together.

Is there a more flexible way so that auto filter will not affect the
shading. It looks strange when a sheet is printed.

Hank

it was just an example

select your range and then conditional formatting.

let's say i have a5 to m170 selected
enter this as the formula
=MOD(SUBTOTAL(3,$A5:$A$170),2)=0
select your color and see if it works.

--


Gary


"HH" wrote in message
.. .
Thanks Gary,

I replaced your code with the one I had in conditional formating. Unless I
missed something, that code shades the entire worksheet. I'm trying keep
every other row shaded - with or without auto filter being used.

Hank
"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
i think gord posted this

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


--


Gary


"HH" wrote in message
...
I use =MOD(ROW(),2)=1 to shade every other row in my worksheet.

When auto filter is used it will not keep every other row shaded. Often
shaded rows are together.

Is there a more flexible way so that auto filter will not affect the
shading. It looks strange when a sheet is printed.

Hank

Gary,
I tried it on a new worksheet. Nothing happened.

I tried it on a work sheet with the origional formula. After using the new
formula, all rows were the same color.

If it makes a difference, I use Excel 2003, and Window XP. One computer has
Vista. I must be doing something different than you are doing. Confused!

"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
it was just an example

select your range and then conditional formatting.

let's say i have a5 to m170 selected
enter this as the formula
=MOD(SUBTOTAL(3,$A5:$A$170),2)=0
select your color and see if it works.

--


Gary


"HH" wrote in message
.. .
Thanks Gary,

I replaced your code with the one I had in conditional formating.
Unless I missed something, that code shades the entire worksheet. I'm
trying keep every other row shaded - with or without auto filter being
used.

Hank
"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
i think gord posted this

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


--


Gary


"HH" wrote in message
...
I use =MOD(ROW(),2)=1 to shade every other row in my worksheet.

When auto filter is used it will not keep every other row shaded.
Often shaded rows are together.

Is there a more flexible way so that auto filter will not affect the
shading. It looks strange when a sheet is printed.

Hank

Select rows 1 through 200 then FormatCFFormula is:

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

Note the position of the $ signs..........do not alter.

Pick a color and OK.


Gord Dibben MS Excel MVP

On Tue, 8 Jul 2008 19:25:39 -0500, "HH" wrote:

Gary,
I tried it on a new worksheet. Nothing happened.

I tried it on a work sheet with the origional formula. After using the new
formula, all rows were the same color.

If it makes a difference, I use Excel 2003, and Window XP. One computer has
Vista. I must be doing something different than you are doing. Confused!

"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
it was just an example

select your range and then conditional formatting.

let's say i have a5 to m170 selected
enter this as the formula
=MOD(SUBTOTAL(3,$A5:$A$170),2)=0
select your color and see if it works.

--


Gary


"HH" wrote in message
.. .
Thanks Gary,

I replaced your code with the one I had in conditional formating.
Unless I missed something, that code shades the entire worksheet. I'm
trying keep every other row shaded - with or without auto filter being
used.

Hank
"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
i think gord posted this

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


--


Gary


"HH" wrote in message
...
I use =MOD(ROW(),2)=1 to shade every other row in my worksheet.

When auto filter is used it will not keep every other row shaded.
Often shaded rows are together.

Is there a more flexible way so that auto filter will not affect the
shading. It looks strange when a sheet is printed.

Hank