Colour alternate rows with unknown range

Apple08 · Sep 25, 2018

Hi All

I want to change the color of the alternate rows starting from A3, but with unknown number of columns and rows. I have found the code below but I don't know how to apply to an unknown range, please could I have some advice?

Code:

Dim r As Range
    Set r = Range("A3:A20")
    Dim tmp As Range, i As Integer

    For Each tmp In r.Cells
        i = i + 1
        If i Mod 2 = 0 Then tmp.Interior.Color = RGB(127, 187, 199)
    Next tmp

Apple08 · Sep 26, 2018

It works perfectly! Many thanks!!!

JackDanIce · Sep 26, 2018

You should try to minimize the amount of read/writes to the sheet as it slows execution vs a single read/write

Should also use long instead of integer variables because xl2007 auto converts integers to longs so it's a redundant step. In some cases, can slow execution due to need to redeclare memory allocation due to the change in data type.

Anyway, try:

Rich (BB code):

Sub AltCol()

    Dim r       As Range
    Dim i       As Long
    Dim x       As Long
    Dim y       As Long

    'Where the column index number 1 maps to A, 2 to B... 26 to Z etc to find the last row in column A
    x = Cells(Rows.Count, 1).End(xlUp).Row
    'Where row 3 is the header row and so assumed to contain the last used column
    y = Cells(3, Columns.Count).End(xlToLeft).Column
    Set r = Cells(3, 1).Resize(, y)
    
    For i = 3 To x Step 2
         If Len(Cells(i,1).Value) Then Set r = Union(r, Cells(i, 1).Resize(, y))
    Next i
    
    r.Interior.Color = RGB(127, 187, 199)
    
    Set r = Nothing
    
End Sub

My Aswer Is This · Sep 26, 2018

Apple08 said:
It works perfectly! Many thanks!!!

Glad we could help.
Original code by one poster I just modified his code a little.

r1998 · Sep 26, 2018

Thanks

Apple08 · Sep 26, 2018

Thank you JackDanIce, I would like to add an auto sum formula to the last row (not last row + 1) of the repot from cell B4 to the last row - 1 then do the same to each column up to the last column. E.g. =sum(B4:last row-1), =sum(C4:last row - 1) and so one. I wonder how can I do that please?

JackDanIce · Sep 26, 2018

You're welcome, you should be able to adjust the row numbers if not correct, however, try:

Rich (BB code):

Sub AltCol()

    Dim r       As Range
    Dim i       As Long
    Dim x       As Long
    Dim y       As Long

    x = Cells(Rows.Count, 1).End(xlUp).Row
    y = Cells(3, Columns.Count).End(xlToLeft).Column
    Set r = Cells(3, 1).Resize(, y)
    
    For i = 3 To x Step 2
        Set r = Union(r, Cells(i, 1).Resize(, y))
    Next i
    
    'Sum Formula where 4 is first row with data, x is last row with data and starting column index 1 (A) to last column y based on row 3
    Cells(x, 1).Resize(, y).Formula = Replace("=SUM(A4:A@)", "@", x - 1)"
    
    r.Interior.Color = RGB(127, 187, 199)
    
    Set r = Nothing
    
End Sub

TotallyConfused · Sep 26, 2018

Hello Apple08

I hope this code is what you are looking for. It determines the number of rows and also the number of columns, so this should work with any number of them.

Code:

Sub HighlightRow()
  Dim R As Long
  Dim C As Long
   For R = 3 To Cells(Rows.Count, "A").End(xlUp).Row Step 2
      For C = 1 To Cells(3, Columns.Count).End(xlToLeft).Column
         Cells(R, C).Interior.Color = RGB(255, 255, 153)
      Next C
   Next R
End Sub

I wasn’t sure from your description, whether you want the third row (A3) to be highlighted, or do you want the highlighting to start with the fourth row (A4)? The way the code is now, it will highlight starting with A3. If you wish to start highlighting at A4 then change the following line of code:

Code:

   For R = 3 To Cells(Rows.Count, "A").End(xlUp).Row Step 2

to read as:

   For R = 4 To Cells(Rows.Count, "A").End(xlUp).Row Step 2

I hope this works.

TotallyConfused

Apple08 · Sep 26, 2018

To JackDanIce: I have syntax error on the auto sum formula...

JackDanIce · Sep 26, 2018

I did make an edit to the code, you may have picked up prior. Try:

Code:

Sub AltCol()

    Dim r       As Range
    Dim i       As Long
    Dim x       As Long
    Dim y       As Long

    x = Cells(Rows.Count, 1).End(xlUp).Row
    y = Cells(3, Columns.Count).End(xlToLeft).Column
    Set r = Cells(3, 1).Resize(, y)
    
    For i = 3 To x Step 2
        Set r = Union(r, Cells(i, 1).Resize(, y))
    Next i
    
    'Sum Formula where x is the last row and 4 is the first row with data
    Cells(x, 1).Resize(, y).Formula = Replace("=SUM(A4:A@)", "@", x - 1)
    
    r.Interior.Color = RGB(127, 187, 199)
    
    Set r = Nothing
    
End Sub

Apple08 · Sep 26, 2018

Thanks Jack, it looks fine. However I don't need a formula in the last row of column A as they are a text column. Is it able to make that happen? I have tried to change the A@ to B@ but the totals are all shifted to the left.

Colour alternate rows with unknown range

Apple08

Active Member

Apple08

Active Member

Excel Facts

JackDanIce

Well-known Member

My Aswer Is This

Well-known Member

r1998

Board Regular

Apple08

Active Member

JackDanIce

Well-known Member

TotallyConfused

Board Regular

Apple08

Active Member

JackDanIce

Well-known Member

Apple08

Active Member

Similar threads

Forum statistics

Share this page

Colour alternate rows with unknown range

Active Member

Active Member

Excel Facts

Well-known Member

Well-known Member

Board Regular

Active Member

Well-known Member

Board Regular

Active Member

Well-known Member

Active Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock