Code assistance

[SF]

Hi,

I need to create timesheets (12 sheets). Each sheet will have Employee Name
in the first column and a dtae range of respective month. Is there a way to:

1 - Populate days of a given month across the worksheet (say starting from
B2)
2 - Determince if the date fall under Staurday or Sunday, if this tru,
highlighted cell with Yelow color

SF

 

[Joe]

Hi SF,

Try this ....
The code is given below...



Private Sub CommandButton1_Click()
' Will add a sheet and put all the dates of a month in 2nd column
' with weekends in Grey Color

Dim Y, M, D As Variant
Const CGrey As Integer = 15 ' or Assign the color code of ur choice
Dim i As Integer


M = Month(Now()) ' or Assign the number of the month (1, 2, ....,
12)
Y = Year(Now()) ' or Assign ur choice. (2007, 2008, ...)
i = 1

Sheets.Add
ActiveSheet.Name = "DD" 'Assign Ur text here

Do While Month(DateSerial(Y, M, i)) = M
With ActiveSheet
.Cells(i, 2) = DateSerial(Y, M, i)
D = Weekday(.Cells(i, 2), vbMonday)
If D = 6 Or D = 7 Then .Cells(i, 2).Interior.ColorIndex =
CGrey
End With
i = i + 1
Loop

End Sub




Check and let me know....

HTH,
Joe

 

[Joe]

Hi SF,


Try the code below and let me know......




Private Sub CommandButton1_Click()
' Will add a sheet and put all the dates of a month in 2nd column
' with weekends in Grey Color

Dim Y, M, D, WD As Variant
Const CGrey As Integer = 15
' or Assign the color code of ur choice



M = Month(Now())
' or Assign the number of the month (1, 2, ...., 12)

Y = Year(Now())
' or Assign ur choice. (2007, 2008, ...)

D = 1

Sheets.Add
ActiveSheet.Name = "DD"
'Assign Ur text here

Do While Month(DateSerial(Y, M, D)) = M
With ActiveSheet
.Cells(D, 2) = DateSerial(Y, M, D)
WD = Weekday(.Cells(D, 2), vbMonday)
If WD = 6 Or WD = 7 Then _
.Cells(D, 2).Interior.ColorIndex = CGrey
End With
D = D + 1
Loop

End Sub




HTH
Joe

 

[SF]

Thank you very much,

The code work very well.

SF

 

[Joe]

Thank you very much,

The code work very well.

SF
















- Show quoted text -

Nice...
Welcome.

Joe