Code assistance
 

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

Code assistance
 

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

Code assistance
 

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

Code assistance
 

Thank you very much,

The code work very well.

SF

"Joe" wrote in message
...
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

Code assistance
 

On Jan 2, 2:09*pm, "SF" wrote:
Thank you very much,

The code work very well.

SF

"Joe" wrote in message

...



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- Hide quoted text -

- Show quoted text -

Nice...
Welcome.

Joe