Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Format and auto filter
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Format and auto filter
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Format and auto filter
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Format and auto filter
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Format and auto filter
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Format and auto filter
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Format and auto filter
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 ... 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Format and auto filter
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" wrote in message ... 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 ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Auto/conditional format | Excel Discussion (Misc queries) | |||
DataFilterAuto Filter in excel 2007? | New Users to Excel | |||
ranging the filter cells when doing auto filter | Excel Programming | |||
can I use conditional format on an auto shape | Excel Discussion (Misc queries) | |||
Excel auto filter doesn't recoginize case - won't filter AA from A | Excel Discussion (Misc queries) |