VBA for alternating Rows for Colors

[Livin404]

Greetings,

I have this code and I know I'm fairly close, can someone please drag me across the finish.

My worksheet "Schedule" Which begins on Row 2 and stretches out to Column G. Ideally I would want my first color Row to begin on Row 3 and go down to the last row of data that is in Column A.

I thought it may work, but I'm off slighly I think.

Sub ALT_Rows()
Dim i, count As Long
i = 2
count = Sheets("Schedule1").Cells(Rows.count, "A").End(xlUp).Row
Do While i <= count
Range(Cells(, i, 1), Cells(i, 6)).Interior.Color = RGB(197, 217, 241)
i = i + 1
Range(Cells(, i, 1), Cells(i, 6)).Interior.Color = RGB(197, 217, 241)
i = i + 1

Loop
End Sub

The color should be a light blue.
Thank you very much indeed.

 

[My Aswer Is This]

Try this:
Assuming your sheet is named "Schedule"
Modify if needed:

Sub Worksheet_Schedule()
'Modified 2/25/2022  2:05:11 PM  EST
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Lastrow = Sheets("Schedule").Cells(Rows.Count, "A").End(xlUp).Row

    For i = 3 To Lastrow
        Cells(i, 1).Resize(, 7).Interior.Color = RGB(197, 217, 241)
    Next
Application.ScreenUpdating = True
End Sub

 

[Fluff]

If you want alternating colours as per your title try

Sub Livin()
   Dim i As Long
  
   With Sheets("Schedule")
      For i = 3 To .Range("A" & Rows.count).End(xlUp).Row Step 2
         .Range("A" & i).Resize(, 7).Interior.Color = RGB(197, 217, 241)
      Next i
   End With
End Sub

 

[Micron]

maybe

Sub ALT_Rows()
Dim i As Long, lngCount As Long

i = 2
lngCount = Sheets("Schedule1").Cells(Rows.count, "A").End(xlUp).Row

Do While i <= lngCount
   Range(Cells(i, 1), Cells(i, 6)).Interior.Color = RGB(197, 217, 241)
   i = i + 1
Loop

End Sub

or

Sub ALT_Rows()
Dim i As Long

i = 2
Do While i <= Sheets("Schedule1").Cells(Rows.count, "A").End(xlUp).Row
   Range(Cells(i, 1), Cells(i, 6)).Interior.Color = RGB(197, 217, 241)
   i = i + 1
Loop

End Sub

Suggest you do not use reserved words, such as Count. At least, I would suspect it would be, but I don't have a list for Excel. It certainly would be a reserved word for Access. I guess there is some reason why you don't just select the range and format.

 

[Fluff]

If you want to colour all the rows, then no loop is needed.

Sub Livin()
   With Sheets("Schedule")
      .Range("A3", .Range("A" & Rows.count).End(xlUp)).Resize(, 7).Interior.Color = RGB(197, 217, 241)
   End With
End Sub

 

[Livin404]

If you want alternating colours as per your title try

Sub Livin()
   Dim i As Long
 
   With Sheets("Schedule")
      For i = 3 To .Range("A" & Rows.count).End(xlUp).Row Step 2
         .Range("A" & i).Resize(, 7).Interior.Color = RGB(197, 217, 241)
      Next i
   End With
End Sub

Exactly right- thank you, !

 

[Livin404]

Try this:
Assuming your sheet is named "Schedule"
Modify if needed:

Sub Worksheet_Schedule()
'Modified 2/25/2022  2:05:11 PM  EST
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Lastrow = Sheets("Schedule").Cells(Rows.Count, "A").End(xlUp).Row

    For i = 3 To Lastrow
        Cells(i, 1).Resize(, 7).Interior.Color = RGB(197, 217, 241)
    Next
Application.ScreenUpdating = True
End Sub

Thank you, it's amazing how many subtle different ways one comes up with codes. You definately got me going in the right direction.

 

[Fluff]

Exactly right- thank you, !

Glad we could help & thanks for the feedback.

 

[My Aswer Is This]

Thank you, it's amazing how many subtle different ways one comes up with codes. You definately got me going in the right direction.

When using Excel Vba there is nearly always several different ways to do everything.
I was not sure what alternating rows mean.
Does it mean every other row?

 

[Fluff]

Does it mean every other row?

Yes it does.