Solved: Colour cells using multiple start/end dates

[tammyl]

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

[Bob Phillips]

No it isn't clear as to how the spreadsheet is actually used. You use terms like 'user input' but don't say what that is.

It would be better to explain in terms of the data and the process, preferably with the workbook example.

[tammyl]

Hi xld,

i have attached a sample of my roster spreadsheet.

I input a start date in cell Z4 and the duration in cell AA4.
The rows in columns have a formula to update the dates accordingly to the start & duration pattern.
Then for each cell in range A6:W38 if activecell is between the start date & end date of the active row in columns Z & AB it is coloured.
Below is the code i use. Please note i'm a newbie to vba so it may not be written correct but so far it works at basic level, just not very flexible.
What i'm trying to do is:
a) remove the need to filldown in the columns Z & AB (maybe for/next/loop?)
b) prompt user to input multiple start & end dates so that mid-year when i have a roster change which is a non-standard duration 21days on/21days off the vba code can handle.



For Each c In Range("A6:W38")
c.Select
If c <> "" Then
For iRow = 4 To iLRow ' start & end date range
If c >= ws.Cells(iRow, sd) And c <= ws.Cells(iRow, ed) Then
c.Interior.ColorIndex = rcColor
End If
Next iRow
End If
Range("A3").Select
Else: ActiveCell.Offset(1, 0).Select
End If
Next c


Hope i have explained better. Hope someone can assist.
Cheers
tammyl

[tammyl]

Ok, I'm trying to attack this in smaller chunks as it's just got me baffled.

I'm trying to eliminate the need to have a 'filldown' date in column Z4 as per original post.

What i wish to do is type in the start date & a duration period. Eg: 1/1/2009, 14 days duration.
Get code to loop for the count of the 'Duration'.

I can't get the loop to break at the count.
It keeps going to the 'Next c' and the count continues past Dur=14.
Pls refer to spreadsheet from earlier post for sample of calendar range.



Dim c As Range
Dim CalRng As Range

Set CalRng = Range("A6:G11,I6:O11,Q6:W11," & _
"A14:G20,I14:O20,Q14:W20," & _
"A23:G29,I23:O29,Q23:W29," & _
"A32:G38,I32:O38,Q32:W38")
SDate = Range("Z4") '1/1/2009
Dur = Range("AA4") '14




Count = 0
Do
For Each c In CalRng
c.Select
If c <> "" Then
If c >= SDate Then
c.Interior.ColorIndex = rcColor
Count = Count + 1
End If
Else
Count = 0
End If
Next c
Loop Until Count = Dur


Hope someone can assist.
Cheers Tammy