Color every second Row

JorgenKjer

Board Regular
Joined
Aug 1, 2016
Messages
65
Office Version
  1. 2013
Platform
  1. Windows
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
 
Last edited:

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Code:
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
 
Upvote 0
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...
Code:
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
 
Upvote 0
Hi pbornemeier

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

Regards
Jorgen
 
Last edited:
Upvote 0
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
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,507
Messages
6,114,029
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top