Alternate Row Colors in Range

I've come up with the following to alternate row colors within a specified range:

Sub AlternateRowColors()
Dim lastRow as Long

lastRow = Range("A1").End(xlDown).Row

For Each Cell In Range("A1:A" & lastRow) ''change range accordingly
    If Cell.Row Mod 2 = 1 Then ''highlights row 2,4,6 etc|= 0 highlights 1,3,5
        Cell.Interior.ColorIndex = 15 ''color to preference
    Else
        Cell.Interior.ColorIndex = xlNone ''color to preference or remove
    End If
Next Cell

End Sub

That works, but is there a simpler method?

The following lines of code may be removed if your data contains no pre-exisiting colors:

    Else
        Cell.Interior.ColorIndex = xlNone

excel vba

Edit Report

1 Answer

'--- Alternate Row color, only non-hidden rows count

Sub Test()

Dim iNumOfRows As Integer, iStartFromRow As Integer, iCount As Integer
iNumOfRows = Range("D61").End(xlDown).Row '--- counts Rows down starting from D61

For iStartFromRow = 61 To iNumOfRows

    If Rows(iStartFromRow).Hidden = False Then '--- only non-hidden rows matter

        iCount = iCount + 1

        If iCount - 2 * Int(iCount / 2) = 0 Then
            Rows(iStartFromRow).Interior.Color = RGB(220, 230, 241)
        Else
            Rows(iStartFromRow).Interior.Color = RGB(184, 204, 228)
        End If

    End If
Next iStartFromRow

End Sub

answered 2013-04-19T01:33:23.057

Alternate Row Colors in Range

1 Answer

Your Answer