Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Assistance with VBA code on MAC | Excel Programming | |||
Assistance with code Please | Excel Discussion (Misc queries) | |||
I need assistance getting VBA code to do the following... | Excel Programming | |||
Code assistance please | Excel Programming | |||
VBA Code Assistance | Excel Programming |