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" <(E-Mail Removed)> wrote in message
news9zck.22492$(E-Mail Removed)...
>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
news:(E-Mail Removed)...
>i think gord posted this
>
> =MOD(SUBTOTAL(3,$A1:$A$2),2)=0
>
>
> --
>
>
> Gary
>
>
> "HH" <(E-Mail Removed)> wrote in message
> news9zck.22492$(E-Mail Removed)...
>>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" <(E-Mail Removed)> wrote in message
news:6QHck.18806$(E-Mail Removed)...
> 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
> news:(E-Mail Removed)...
>>i think gord posted this
>>
>> =MOD(SUBTOTAL(3,$A1:$A$2),2)=0
>>
>>
>> --
>>
>>
>> Gary
>>
>>
>> "HH" <(E-Mail Removed)> wrote in message
>> news9zck.22492$(E-Mail Removed)...
>>>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
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:6QHck.18806$(E-Mail Removed)...
>> 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
>> news:(E-Mail Removed)...
>>>i think gord posted this
>>>
>>> =MOD(SUBTOTAL(3,$A1:$A$2),2)=0
>>>
>>>
>>> --
>>>
>>>
>>> Gary
>>>
>>>
>>> "HH" <(E-Mail Removed)> wrote in message
>>> news9zck.22492$(E-Mail Removed)...
>>>>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 Format>CF>Formula 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" <(E-Mail Removed)> 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
>news:(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
>> news:6QHck.18806$(E-Mail Removed)...
>>> 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
>>> news:(E-Mail Removed)...
>>>>i think gord posted this
>>>>
>>>> =MOD(SUBTOTAL(3,$A1:$A$2),2)=0
>>>>
>>>>
>>>> --
>>>>
>>>>
>>>> Gary
>>>>
>>>>
>>>> "HH" <(E-Mail Removed)> wrote in message
>>>> news9zck.22492$(E-Mail Removed)...
>>>>>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 I found out why it won't work for me. I don't understand why but
the sheet I have been trying to use the code on is a "summary" sheet
produced with the following code:
Sub SignOutLog()
'
'

'
Sheets("SignOutLog").Select
' Clear the existing values (if any)
Range("$A$2:$m$60").Value = ""
' J tracks the row number on the summary page
' I tracks the sheet number being processed
J = 2
For I = 2 To Sheets.Count
a$ = Sheets(I).Name
' Don't process a sheet if its name Other than "SignOutLog"
' or if the name is blank.
If (a$ = "Birthday") Then GoTo 10
If (a$ = "DepositRecord") Then GoTo 10
If (a$ = "MailLabels") Then GoTo 10
If (a$ = "PmtSummary") Then GoTo 10
If (a$ = "Templat") Then GoTo 10
If (a$ = "ID") Then GoTo 10
If (a$ = "SignOutLog") Then GoTo 10
If (Sheets(a$).Range("$C$1").Value = "") Then GoTo 10
' Process the current sheet
Range("g" + Format(J)).FormulaR1C1 = "='" + a$ + "'!R6C3"
Range("e" + Format(J)).FormulaR1C1 = "='" + a$ + "'!R6C4"
Range("f" + Format(J)).FormulaR1C1 = "='" + a$ + "'!R6C14"
Range("k" + Format(J)).FormulaR1C1 = "='" + a$ + "'!R9C9"
J = J + 1
10 Next I
End Sub

When I use CF with your code, every row is the same color.
I used your code on another "made from scratch" worksheet and it works
perfect.
Any idea why it won't work on the 'summary' sheet?

Hank

"Gord Dibben" <gorddibbATshawDOTca> wrote in message
news:(E-Mail Removed)...
> Select rows 1 through 200 then Format>CF>Formula 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" <(E-Mail Removed)> 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
>>news:(E-Mail Removed)...
>>> 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" <(E-Mail Removed)> wrote in message
>>> news:6QHck.18806$(E-Mail Removed)...
>>>> 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
>>>> news:(E-Mail Removed)...
>>>>>i think gord posted this
>>>>>
>>>>> =MOD(SUBTOTAL(3,$A1:$A$2),2)=0
>>>>>
>>>>>
>>>>> --
>>>>>
>>>>>
>>>>> Gary
>>>>>
>>>>>
>>>>> "HH" <(E-Mail Removed)> wrote in message
>>>>> news9zck.22492$(E-Mail Removed)...
>>>>>>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
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>

Sorry, I mis spoke.
The Code I supplies does not actually PRODUCE the 'summary' sheet. It
Populates a sheet that is named 'summary'

Hank

"HH" <(E-Mail Removed)> wrote in message
news:Jccdk.23697$(E-Mail Removed)...
>I think I found out why it won't work for me. I don't understand why but
>the sheet I have been trying to use the code on is a "summary" sheet
>produced with the following code:
> Sub SignOutLog()
> '
> '
>
> '
> Sheets("SignOutLog").Select
> ' Clear the existing values (if any)
> Range("$A$2:$m$60").Value = ""
> ' J tracks the row number on the summary page
> ' I tracks the sheet number being processed
> J = 2
> For I = 2 To Sheets.Count
> a$ = Sheets(I).Name
> ' Don't process a sheet if its name Other than "SignOutLog"
> ' or if the name is blank.
> If (a$ = "Birthday") Then GoTo 10
> If (a$ = "DepositRecord") Then GoTo 10
> If (a$ = "MailLabels") Then GoTo 10
> If (a$ = "PmtSummary") Then GoTo 10
> If (a$ = "Templat") Then GoTo 10
> If (a$ = "ID") Then GoTo 10
> If (a$ = "SignOutLog") Then GoTo 10
> If (Sheets(a$).Range("$C$1").Value = "") Then GoTo 10
> ' Process the current sheet
> Range("g" + Format(J)).FormulaR1C1 = "='" + a$ + "'!R6C3"
> Range("e" + Format(J)).FormulaR1C1 = "='" + a$ + "'!R6C4"
> Range("f" + Format(J)).FormulaR1C1 = "='" + a$ + "'!R6C14"
> Range("k" + Format(J)).FormulaR1C1 = "='" + a$ + "'!R9C9"
> J = J + 1
> 10 Next I
> End Sub
>
> When I use CF with your code, every row is the same color.
> I used your code on another "made from scratch" worksheet and it works
> perfect.
> Any idea why it won't work on the 'summary' sheet?
>
> Hank
>
> "Gord Dibben" <gorddibbATshawDOTca> wrote in message
> news:(E-Mail Removed)...
>> Select rows 1 through 200 then Format>CF>Formula 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" <(E-Mail Removed)> 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
>>>news:(E-Mail Removed)...
>>>> 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" <(E-Mail Removed)> wrote in message
>>>> news:6QHck.18806$(E-Mail Removed)...
>>>>> 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
>>>>> news:(E-Mail Removed)...
>>>>>>i think gord posted this
>>>>>>
>>>>>> =MOD(SUBTOTAL(3,$A1:$A$2),2)=0
>>>>>>
>>>>>>
>>>>>> --
>>>>>>
>>>>>>
>>>>>> Gary
>>>>>>
>>>>>>
>>>>>> "HH" <(E-Mail Removed)> wrote in message
>>>>>> news9zck.22492$(E-Mail Removed)...
>>>>>>>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
>>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>
>
>