Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Alternate row coloring
I've seen routines posted that color alternate rows in a range or sheet,
but I have a little different need. I use the following routine a couple of times a month to fax in a food order to a food distributing company. It filters out foods I don't need from the list before faxing it. Sub FaxIt() Columns("A:A").AutoFilter Field:=1, Criteria1:="<" Application.ActivePrinter = "Brother PC-FAX on BMFC:" ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _ "Brother PC-FAX on BMFC:", Collate:=True Columns("A:A").AutoFilter End Sub I would like the filtered list to have alternate row coloring. Any help? -- David |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Alternate row coloring
Sub ColourMe()
Dim rng As Range Dim c As Range Dim blnColour As Boolean Set rng = ActiveSheet.Range("A2:A" & ActiveSheet.Range("A65536").End(xlUp).Row) Set rng = rng.SpecialCells(xlCellTypeVisible) For Each c In rng If blnColour Then c.EntireRow.Interior.ColorIndex = 3 blnColour = Not blnColour Else c.EntireRow.Interior.ColorIndex = xlColorIndexNone blnColour = Not blnColour End If Next c Set c = Nothing Set rng = Nothing End Sub Sub FaxIt() Columns("A:A").AutoFilter Field:=1, Criteria1:="<" Application.ActivePrinter = "Brother PC-FAX on BMFC:" Call ColourMe ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _ "Brother PC-FAX on BMFC:", Collate:=True Columns("A:A").AutoFilter End Sub -- HTH, Dianne In , David Turner typed: I've seen routines posted that color alternate rows in a range or sheet, but I have a little different need. I use the following routine a couple of times a month to fax in a food order to a food distributing company. It filters out foods I don't need from the list before faxing it. Sub FaxIt() Columns("A:A").AutoFilter Field:=1, Criteria1:="<" Application.ActivePrinter = "Brother PC-FAX on BMFC:" ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _ "Brother PC-FAX on BMFC:", Collate:=True Columns("A:A").AutoFilter End Sub I would like the filtered list to have alternate row coloring. Any help? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Alternate row coloring
I've written two routines, one to color the rows, the other to remove the color. I picked a
light gray for the shaded rows. Pick another color index value if you like by changing the number 40 in this line CI(1) = 40 to some another value. Sub FaxIt() Columns("A:A").AutoFilter Field:=1, Criteria1:="<" ColorRows '<<<<< Application.ActivePrinter = "Brother PC-FAX on BMFC:" ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _ "Brother PC-FAX on BMFC:", Collate:=True RemoveColor '<<<<< Columns("A:A").AutoFilter End Sub Sub ColorRows() Dim c As Range Dim CI(0 To 1) As Long Dim i As Long Dim Rng As Range CI(0) = xlColorIndexNone CI(1) = 40 i = 0 Set Rng = ActiveSheet.UsedRange Rng.Interior.ColorIndex = CI(i) For Each c In Rng.Columns("A").SpecialCells(xlCellTypeVisible) Rng.Rows(c.Row).Interior.ColorIndex = CI(i) i = 1 - i Next c End Sub Sub RemoveColor() ActiveSheet.UsedRange.Interior.ColorIndex = xlColorIndexNone End Sub On Fri, 07 Nov 2003 12:40:51 -0800, David Turner wrote: I've seen routines posted that color alternate rows in a range or sheet, but I have a little different need. I use the following routine a couple of times a month to fax in a food order to a food distributing company. It filters out foods I don't need from the list before faxing it. Sub FaxIt() Columns("A:A").AutoFilter Field:=1, Criteria1:="<" Application.ActivePrinter = "Brother PC-FAX on BMFC:" ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _ "Brother PC-FAX on BMFC:", Collate:=True Columns("A:A").AutoFilter End Sub I would like the filtered list to have alternate row coloring. Any help? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Alternate row coloring
Myrna Larson wrote
I've written two routines, one to color the rows, the other to remove the color. I picked a light gray for the shaded rows. Many thanks, Myrna Actually 40 returns a peach color here. I chose 15 for light grey. And I like the reset sub to get things back to square one. One slight modification. Rows 1-5 contain stuff I would like to exclude from the coloring and start with row 6. -- David |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Alternate row coloring
Dianne wrote
Sub ColourMe() Thanks for trying Dianne, but for some reason your routine didn't work. -- David |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Alternate row coloring
Don Guillett wrote
I thought I answered this a day or two ago? Sub colorvisible() x = Range("a" & Rows.Count).End(xlUp) Rows("1:" & x).Interior.ColorIndex = xlNone For i = 2 To x Step 2 Cells(i, 1).SpecialCells(xlCellTypeVisible) _ .EntireRow.Interior.ColorIndex = 6 Next i End Sub Indeed you did. And I replied with the observation that without changing line 1 to x = Range("a" & Rows.Count).End(xlUp).Row, it bombed with a 'Type mismatch error. Even with the modification, when called after setting AutoFilter, my whole sheet turns yellow. Works ok if I don't AutoFilter any thing. -- David |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Alternate row coloring
You are correct. I thought I had tested.
Glad Myrna's worked for you. -- Don Guillett SalesAid Software "David Turner" wrote in message ... Don Guillett wrote I thought I answered this a day or two ago? Sub colorvisible() x = Range("a" & Rows.Count).End(xlUp) Rows("1:" & x).Interior.ColorIndex = xlNone For i = 2 To x Step 2 Cells(i, 1).SpecialCells(xlCellTypeVisible) _ .EntireRow.Interior.ColorIndex = 6 Next i End Sub Indeed you did. And I replied with the observation that without changing line 1 to x = Range("a" & Rows.Count).End(xlUp).Row, it bombed with a 'Type mismatch error. Even with the modification, when called after setting AutoFilter, my whole sheet turns yellow. Works ok if I don't AutoFilter any thing. -- David |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Alternate row coloring
To exclude the first 5 rows, here's the "easy" way out <g:
Sub ColorRows() Dim c As Range Dim CI(0 To 1) As Long Dim i As Long Dim Rng As Range CI(0) = xlColorIndexNone CI(1) = 15 i = 0 Set Rng = ActiveSheet.UsedRange Rng.Interior.ColorIndex = CI(i) For Each c In Rng.Columns("A").SpecialCells(xlCellTypeVisible) Rng.Rows(c.Row).Interior.ColorIndex = CI(i) i = 1 - i Next c Rows("1:5").Interior.ColorIndex = CI(0) '<<<<< End Sub On Fri, 07 Nov 2003 13:55:54 -0800, David Turner wrote: Myrna Larson wrote I've written two routines, one to color the rows, the other to remove the color. I picked a light gray for the shaded rows. Many thanks, Myrna Actually 40 returns a peach color here. I chose 15 for light grey. And I like the reset sub to get things back to square one. One slight modification. Rows 1-5 contain stuff I would like to exclude from the coloring and start with row 6. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Alternate row coloring
Hi, Don:
I don't think that will work correctly. You shade each even-numbered row *if* it's visible. You need to shade every other visible row, regardless of its abolute row number. Ignoring his later post that he wants to start with row 6, let's say just rows 2 and 4 are hidden. Your routine will end up with rows 1, 3, and 5 all visible and having no shading. You should shade either 1 and 5, or 3. Myrna Larson On Fri, 7 Nov 2003 15:28:42 -0600, "Don Guillett" wrote: I thought I answered this a day or two ago? Sub colorvisible() x = Range("a" & Rows.Count).End(xlUp) Rows("1:" & x).Interior.ColorIndex = xlNone For i = 2 To x Step 2 Cells(i, 1).SpecialCells(xlCellTypeVisible) _ .EntireRow.Interior.ColorIndex = 6 Next i End Sub |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Alternate row coloring
Myrna Larson wrote
Rows("1:5").Interior.ColorIndex = CI(0) So simple when you know how <g Thanks so much. -- David |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Coloring | Excel Worksheet Functions | |||
Alternate Row Coloring | Excel Discussion (Misc queries) | |||
tab coloring | Excel Worksheet Functions | |||
Coloring Text | Excel Discussion (Misc queries) | |||
coloring cells | Excel Discussion (Misc queries) |