tammyl
11-25-2008, 11:04 PM
Hi All,
I'm trying to automate colouring a yearly roster calendar in excel via vba code.
My mission: Colour interior of calendar date cells if date falls between start & end dates. This needs to incorporate a cycle of rostered days on & rostered days off (user input eg: 14days).
Colour interior of cell if rostered day = ON.
Eg: user input start 1/1/2009 & user input end date 23/6/2009
user input start 30/6/2009 & user input end date 31/12/2009
From 1/1/2009 loop for 14days = roster ON
Following 14days = roster OFF
Continue until end date 23/6/2009 reached.
Start again at 30/6/2009, as above.
I've been able to do this with a fixed start date and then a column filled with a start/end date for every 14days but this is time consuming for the user to input the dates for every 14day loop and i can't manage to work out code for multiple start & end dates to allow for the roster break mid year.
Below sample of input from spreadsheet to run the code.
Start Duration End
1/1/2009 14 15/1/2009
29/1/2009 14 12/2/2009
26/2/2009 14 12/3/2009
For iRow = 4 To iLRow
If c >= ws.Cells(iRow, sd) And c <= ws.Cells(iRow, ed) Then
c.Interior.ColorIndex = rcColor
End If
Next iRow
I hope this is clear and that somebody can assist. Any help much appreciated.
Cheers
tammyl :dunno
I'm trying to automate colouring a yearly roster calendar in excel via vba code.
My mission: Colour interior of calendar date cells if date falls between start & end dates. This needs to incorporate a cycle of rostered days on & rostered days off (user input eg: 14days).
Colour interior of cell if rostered day = ON.
Eg: user input start 1/1/2009 & user input end date 23/6/2009
user input start 30/6/2009 & user input end date 31/12/2009
From 1/1/2009 loop for 14days = roster ON
Following 14days = roster OFF
Continue until end date 23/6/2009 reached.
Start again at 30/6/2009, as above.
I've been able to do this with a fixed start date and then a column filled with a start/end date for every 14days but this is time consuming for the user to input the dates for every 14day loop and i can't manage to work out code for multiple start & end dates to allow for the roster break mid year.
Below sample of input from spreadsheet to run the code.
Start Duration End
1/1/2009 14 15/1/2009
29/1/2009 14 12/2/2009
26/2/2009 14 12/3/2009
For iRow = 4 To iLRow
If c >= ws.Cells(iRow, sd) And c <= ws.Cells(iRow, ed) Then
c.Interior.ColorIndex = rcColor
End If
Next iRow
I hope this is clear and that somebody can assist. Any help much appreciated.
Cheers
tammyl :dunno