Conditional Format and auto filter

[HH]

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 Keramidas]

i think gord posted this

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

 

[HH]

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]

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.

 

[HH]

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!

 

[Gord Dibben]

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

 

[HH]

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

 

[HH]

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

Hank