Results 1 to 6 of 6

Thread: Color every second Row
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Aug 2016
    Location
    Denmark
    Posts
    51
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Color every second Row

    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 by JorgenKjer; Aug 6th, 2019 at 10:49 AM.

  2. #2
    Board Regular pbornemeier's Avatar
    Join Date
    May 2005
    Location
    Virginia Beach, VA USA
    Posts
    3,656
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Color every second Row

    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
    Phil

    - Use CODE tags to keep your code formatted. See: BB Tags
    - How to attach Screenshots
    - Try searching for your answer first, see how
    - Test and validate results for all code on a copy of your worksheet!! How do you use the code you just found?
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

  3. #3
    Board Regular
    Join Date
    Apr 2010
    Posts
    167
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Color every second Row

    Quote Originally Posted by JorgenKjer View Post
    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

  4. #4
    Board Regular
    Join Date
    Aug 2016
    Location
    Denmark
    Posts
    51
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Color every second Row

    Hi pbornemeier

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

    Regards
    Jorgen
    Last edited by JorgenKjer; Aug 6th, 2019 at 12:39 PM.

  5. #5
    Board Regular
    Join Date
    Aug 2016
    Location
    Denmark
    Posts
    51
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Color every second Row

    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 by JorgenKjer; Aug 6th, 2019 at 12:53 PM.

  6. #6
    Board Regular pbornemeier's Avatar
    Join Date
    May 2005
    Location
    Virginia Beach, VA USA
    Posts
    3,656
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Color every second Row

    You're welcome.
    Phil

    - Use CODE tags to keep your code formatted. See: BB Tags
    - How to attach Screenshots
    - Try searching for your answer first, see how
    - Test and validate results for all code on a copy of your worksheet!! How do you use the code you just found?
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •