Colour alternate rows with unknown range

[Apple08]

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?

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

 

[My Aswer Is This]

So you do not know what rows or what columns and did not say what color you want these rows.
So how would we know?

 

[Logit]

.
Here is code that changes every 3rd row a selected color. You can change the Mod from 3 to 2 for everyother row.

You will manually highlighted the range desired, then make the selection from a Combobox. If you don't want a Combobox you can edit the macro for any type control preferred.
If you want to use only one color, forego most all of the macro and use just one section.

Private Sub ComboBox2_Change()
Dim Counter As Integer


Dim index As Integer
index = ComboBox2.ListIndex


Select Case index
    
    Case Is = 0
       'For every row in the current selection...
       For Counter = 1 To Selection.Rows.Count
           'If the row is an odd number (within the selection)...
           If Counter Mod 3 = 1 Then
               'Set the pattern to Lt Grey.
               Selection.Rows(Counter).Interior.Color = RGB(192, 192, 192)
           End If
       Next
    Case Is = 1
        'For every row in the current selection...
       For Counter = 1 To Selection.Rows.Count
           'If the row is an odd number (within the selection)...
           If Counter Mod 3 = 1 Then
               'Set the pattern to Lt Green.
               Selection.Rows(Counter).Interior.Color = RGB(204, 255, 204)
           End If
       Next
    Case Is = 2
        'For every row in the current selection...
       For Counter = 1 To Selection.Rows.Count
           'If the row is an odd number (within the selection)...
           If Counter Mod 3 = 1 Then
               'Set the pattern to Lt Yellow.
               Selection.Rows(Counter).Interior.Color = RGB(255, 255, 153)
           End If
       Next
    Case Is = 3
        'For every row in the current selection...
       For Counter = 1 To Selection.Rows.Count
           'If the row is an odd number (within the selection)...
           If Counter Mod 3 = 1 Then
               'Set the pattern to Lt Blue.
               Selection.Rows(Counter).Interior.Color = RGB(204, 255, 255)
           End If
       Next
    Case Is = 4
        'For every row in the current selection...
       For Counter = 1 To Selection.Rows.Count
           'If the row is an odd number (within the selection)...
           If Counter Mod 3 = 1 Then
               'Set the pattern to Lt Tan.
               Selection.Rows(Counter).Interior.Color = RGB(255, 204, 153)
           End If
       Next
    Case Is = 5
        'For every row in the current selection...
       For Counter = 1 To Selection.Rows.Count
           'If the row is an odd number (within the selection)...
           If Counter Mod 3 = 1 Then
               'Set the pattern to Lt Red.
               Selection.Rows(Counter).Interior.Color = RGB(255, 220, 204)
           End If
       Next
     Case Is = 6
        'For every row in the current selection...
       For Counter = 1 To Selection.Rows.Count
           'If the row is an odd number (within the selection)...
           If Counter Mod 3 = 1 Then
               'Set the pattern to White.
               Selection.Rows(Counter).Interior.Color = RGB(255, 255, 255)
           End If
       Next
    
End Select


End Sub

 

[ColdGeorge]

Apple08

Give this a try

Dim celda As Range
Dim i As Integer
For Each celda In Selection
i = i + 1
If i Mod 2 = 0 Then celda.Interior.Color = RGB(127, 187, 199)
Next celda

 

[Apple08]

Sorry guys, I haven't explained clearly. I want to start from row 3 then change te the alternate row every two rows in light yellow colour. However the number of rows and columns are unknown for the report.

ColdGeorge, your code only change the alternate column but not row. Also it only color up to row 2.

 

[r1998]

Hello Apple08
try this

Sub colooor()
Dim r As Range
Dim i As Integer
    For i = 3 To 30000 Step 2
    If Cells(i, 1) <> "" Then
    Cells(i, 1).EntireRow.Interior.Color = RGB(255, 255, 153)
    End If
    Next i
End Sub

 

[Apple08]

Thanks r1998, it works but it doesn't stop at the last column Is it possible to make it stop there?

 

[My Aswer Is This]

Which column is the last column

 

[Apple08]

Indeed, the last column is unknown. However if it is too complicated to stop at an unknown column, please can I make it stop at column AH?

 

[My Aswer Is This]

Try this:

Sub colooor()
'Modified  9/26/2018  5:16:16 AM  EDT
'r1998 Original code modified
Dim r As Range
Dim i As Integer
Dim LastColumn As Long
    For i = 3 To 30000 Step 2
        If Cells(i, 1) <> "" Then
            Cells(i, 1).Resize(, Cells(i, Columns.Count).End(xlToLeft).Column).Interior.Color = RGB(255, 255, 153)
        End If
    Next i
End Sub