Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
colour alternate rows in filtered list
In VBA, how would I change the interior colour of
alternate rows in a filtered list ie. visible rows only? Thanks, Rosemary |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
colour alternate rows in filtered list
Try this on Chip Pearsons site, it will give you all you need!
http://www.cpearson.com/excel.htm You will find a section on colour formatting here. Simo -- Message posted from http://www.ExcelForum.com |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
colour alternate rows in filtered list
Hi
try the following: - row 1 is the heading row - column A is the filtered column - select row 2:x - goto 'Format - Conditional Format' - enter the following formula: =MOD(SUBTOTAL(3,$A$2:$A2),2) - choose a fomat -----Original Message----- In VBA, how would I change the interior colour of alternate rows in a filtered list ie. visible rows only? Thanks, Rosemary . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
colour alternate rows in filtered list
-----Original Message----- Try this on Chip Pearsons site, it will give you all you need! http://www.cpearson.com/excel.htm You will find a section on colour formatting here. Simon --- Message posted from http://www.ExcelForum.com/ . Simon, Thanks for your reply. I couldn't actually find what I wanted on the site you pointed me to (I'm sure it was there but I am probably too impatient/jaded to weed it out (been working too hard recently!)) but did find useful help at: http://j-walk.com/ss/excel/usertips/tip043.htm For the benefit of anyone else who wants to do something similar, a solution is: With Range(???).SpecialCells(xlCellTypeVisible) .FormatConditions.Delete .FormatConditions.Add Type:=xlExpression, Formula1:= _ "=MOD(ROW(),2)=0" .FormatConditions(1).Interior.ColorIndex = ??? End With Regards, Rosemary |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
colour alternate rows in filtered list
-----Original Message----- Hi try the following: - row 1 is the heading row - column A is the filtered column - select row 2:x - goto 'Format - Conditional Format' - enter the following formula: =MOD(SUBTOTAL(3,$A$2:$A2),2) - choose a fomat -----Original Message----- In VBA, how would I change the interior colour of alternate rows in a filtered list ie. visible rows only? Thanks, Rosemary . . Frank, |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
colour alternate rows in filtered list
-----Original Message----- Hi try the following: - row 1 is the heading row - column A is the filtered column - select row 2:x - goto 'Format - Conditional Format' - enter the following formula: =MOD(SUBTOTAL(3,$A$2:$A2),2) - choose a fomat -----Original Message----- In VBA, how would I change the interior colour of alternate rows in a filtered list ie. visible rows only? Thanks, Rosemary . . Frank |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
colour alternate rows in filtered list
-----Original Message----- Hi try the following: - row 1 is the heading row - column A is the filtered column - select row 2:x - goto 'Format - Conditional Format' - enter the following formula: =MOD(SUBTOTAL(3,$A$2:$A2),2) - choose a fomat -----Original Message----- In VBA, how would I change the interior colour of alternate rows in a filtered list ie. visible rows only? Thanks, Rosemary . . Frank, thank you for your solution (3rd time lucky!). It also works, though I don't really understand why. It also causes some of the hidden rows to get coloured too, in what appears a very random pattern ie. when the list is un- filtered, I see that rows 2, 3, 7, 9, 10, 11 are coloured, whereas when the list is filtered rows 2 7 and 9 are correctly coloured Could you perhaps enlighten me as to how it works? Thanks, Rosemary |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
colour alternate rows in filtered list
Sometimes, it's easier to see what happens if you use that conditional
formatting in a helper column of cells. I added another column and plopped this into E2 (for me) =MOD(SUBTOTAL(3,$A$2:$A2),2) Then I dragged down to match the number of rows in the data. You'll notice that when nothing is filtered, you get: 1,0,1,0,1,0,1.... (alternating 1's/0's) Now filter that list: The visible cells in that helper column still look like an alternating sequence of 1,0,1,0,... The =subtotal() function respects hidden cells (when hidden by an autofilter). And =subtotal(3,$a$2:$a2) counts (the 3 portion) the number of cells that have something in them from A2 to the row you're in. Note that if you used a column that somes contained empty cells (not formulas that evaluated to ""), then you're formula will not work the way you want. wrote: -----Original Message----- Hi try the following: - row 1 is the heading row - column A is the filtered column - select row 2:x - goto 'Format - Conditional Format' - enter the following formula: =MOD(SUBTOTAL(3,$A$2:$A2),2) - choose a fomat -----Original Message----- In VBA, how would I change the interior colour of alternate rows in a filtered list ie. visible rows only? Thanks, Rosemary . . Frank, thank you for your solution (3rd time lucky!). It also works, though I don't really understand why. It also causes some of the hidden rows to get coloured too, in what appears a very random pattern ie. when the list is un- filtered, I see that rows 2, 3, 7, 9, 10, 11 are coloured, whereas when the list is filtered rows 2 7 and 9 are correctly coloured Could you perhaps enlighten me as to how it works? Thanks, Rosemary -- Dave Peterson |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
colour alternate rows in filtered list
Dave,
Thanks very much for taking the time to explain this to me. I finally understand it. Regards, Rosemary -----Original Message----- Sometimes, it's easier to see what happens if you use that conditional formatting in a helper column of cells. I added another column and plopped this into E2 (for me) =MOD(SUBTOTAL(3,$A$2:$A2),2) Then I dragged down to match the number of rows in the data. You'll notice that when nothing is filtered, you get: 1,0,1,0,1,0,1.... (alternating 1's/0's) Now filter that list: The visible cells in that helper column still look like an alternating sequence of 1,0,1,0,... The =subtotal() function respects hidden cells (when hidden by an autofilter). And =subtotal(3,$a$2:$a2) counts (the 3 portion) the number of cells that have something in them from A2 to the row you're in. Note that if you used a column that somes contained empty cells (not formulas that evaluated to ""), then you're formula will not work the way you want. wrote: -----Original Message----- Hi try the following: - row 1 is the heading row - column A is the filtered column - select row 2:x - goto 'Format - Conditional Format' - enter the following formula: =MOD(SUBTOTAL(3,$A$2:$A2),2) - choose a fomat -----Original Message----- In VBA, how would I change the interior colour of alternate rows in a filtered list ie. visible rows only? Thanks, Rosemary . . Frank, thank you for your solution (3rd time lucky!). It also works, though I don't really understand why. It also causes some of the hidden rows to get coloured too, in what appears a very random pattern ie. when the list is un- filtered, I see that rows 2, 3, 7, 9, 10, 11 are coloured, whereas when the list is filtered rows 2 7 and 9 are correctly coloured Could you perhaps enlighten me as to how it works? Thanks, Rosemary -- Dave Peterson . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
alternate row shading on filtered data | Excel Discussion (Misc queries) | |||
How do I count rows in a filtered list when using AutoFilter? | Excel Worksheet Functions | |||
Alternate row shading with a filtered spreadsheet | Excel Discussion (Misc queries) | |||
Define a range containing the first 10 rows of a filtered list | Excel Worksheet Functions | |||
HOW CAN I AUTOMATICALLY INSERT ALTERNATE BLANK ROWS IN A LIST? | Excel Discussion (Misc queries) |