Alternate row coloring

[David Turner]

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?

 

[Dianne]

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

 

[Myrna Larson]

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

 

[Don Guillett]

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

 

[David Turner]

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

Dianne wrote

Sub ColourMe()

Thanks for trying Dianne, but for some reason your routine didn't work.

 

[David Turner]

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.

 

[Don Guillett]

You are correct. I thought I had tested.
Glad Myrna's worked for you.

 

[Myrna Larson]

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

 

[Myrna Larson]

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

 

[David Turner]

Myrna Larson wrote

Rows("1:5").Interior.ColorIndex = CI(0)

So simple when you know how <g>

Thanks so much.