Color every second Row

[JorgenKjer]

Hi

Is it possible to create a code that color every second Row with e.g. RGB: 221, 235,247

Two pages in the Workbook, "Data" and "FrontPage", should not be colored.
The size ofthe Range on the sheets to be colored can vary in number of rows
Range to be colored always starts at "A7"

Regards
Jorgen

 

[pbornemeier]

Option Explicit

Sub ColorEveryOtherRow()

    Dim wks As Worksheet
    Dim lLastCol  As Long
    Dim lLastRow As Long
    Dim l1stColor As Long
    Dim l2ndColor As Long
    Dim lRowIndex As Long
    
    l1stColor = RGB(221, 235, 247)
    l2ndColor = rgbWhite
    
    For Each wks In ActiveWorkbook.Worksheets
        Select Case wks.Name
        Case "Data", "FrontPage"
            'Do nothing
        Case Else
            With wks
                .AutoFilterMode = False
                lLastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
                If lLastRow > 7 Then
                    lLastCol = .Cells(7, .Columns.Count).End(xlToLeft).Column
                    For lRowIndex = lLastRow To 7 Step -1
                        If .Cells(lRowIndex, 1).Row / 2 = .Cells(lRowIndex, 1).Row \ 2 Then
                            .Range(.Cells(lRowIndex, 1), .Cells(lRowIndex, lLastCol)).Interior.Color = l1stColor
                        Else
                            .Range(.Cells(lRowIndex, 1), .Cells(lRowIndex, lLastCol)).Interior.Color = l2ndColor
                        End If
                    Next
                End If
            End With
        End Select
    Next
           
End Sub

 

[Steve_]

Hi

Is it possible to create a code that color every second Row with e.g. RGB: 221, 235,247

Two pages in the Workbook, "Data" and "FrontPage", should not be colored.
The size ofthe Range on the sheets to be colored can vary in number of rows
Range to be colored always starts at "A7"

Regards
Jorgen

Start here...

Sub ColorIt()
    For Each sh In ThisWorkbook.Sheets
        If sh.Name <> "Data" And sh.Name <> "FrontPage" Then
            For i = 0 To sh.UsedRange.Rows.Count
                If i Mod 2 Then
                    'if Odd number, color this color...
                    'sh.Range("A" & 7 + i).Interior.Color = RGB(221, 235, 247)
                Else
                    'if Even number, color this color...
                    sh.Range("A" & 7 + i).Interior.Color = RGB(221, 235, 247)
                End If
            Next i
        End If
    Next sh
End Sub

 

[JorgenKjer]

Hi pbornemeier

Thank you for your prompt response
The code works perfectly in my Workbook
Thank you very much

Regards
Jorgen

 

[JorgenKjer]

Hi Steve

Thank you for your prompt response
I forgot to mention that all Rows cover more than column "A"
However, I have received help with a code that takes this into account
Thank you very much for your help and effort

Regards
Jorgen

 

[pbornemeier]

You're welcome.