Setting the background color for weekends

[DChambers]

I am using the following code to create a spreadsheet that has all the days of the week for that month and would like to set the background color for both Saturday and Sunday to gray, any Ideas?

' Put in the first day of the current month
    ws.Range("A8").Value = DateSerial(Year(Date), Month(Date), 1)
 
' AutoFill Column "A" with all the dates of the month
    ws.Range("A8").AutoFill Destination:=ws.Range("A8").Resize(DateSerial(Year(Date), Month(Date) + 1, 1) - DateSerial(Year(Date), Month(Date), 1))
 
' AutoFill Column "B" with the days of the week
    ws.Range("B8").Resize(DateSerial(Year(Date), Month(Date) + 1, 1) - DateSerial(Year(Date), Month(Date), 1)).Formula = "=TEXT(A8,""DDDD"")"

 

[Scott Huish]

You can get the number of days in the month like this:

Day(DateSerial(Year(Date), Month(Date) + 1, 0))

and here's another way to get the first day of the month:
Date - Day(Date) + 1

Why not use Conditional Formatting?

Select cell A8:B38 and use the following Conditional Formatting formula:
=AND($A8<>"",WEEKDAY($A8,2)>5)