Does anyone have a template for using Excel as a simple Construct.

[Guest]

We prepare a summary for people who do not know/have Project. The schedule
is simply the task name with cells on the row shaded to indicate the weeks of
the project duration. I am looking for a simple way to enter a start/end
date and have the system calculate and shade the appropriate number of cells
on the row.

 

[Doug Glancy]

This assumes task name in column A, start dates in B and end dates in C.
Lots of nested Ifs to check if not blank, is date, not too many columns,
etc. Paste it into the worksheet module:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim start_date As Range, end_date As Range

'assumes start_date in column B, end_date in C
Set start_date = Cells(Target.Row, 2)
Set end_date = Cells(Target.Row, 3)

'check for entry in column B or C
If Not Intersect(Target, Range(start_date, end_date)) Is Nothing Then
'if both columns have entries
If Not IsEmpty(start_date) Or IsEmpty(end_date) Then
'if both entries are dates
If IsDate(start_date) And IsDate(end_date) Then
If end_date > start_date Then
'Color from Column D to left number of weeks or partial
weeks
'at least 1 week, no more than 253
end_date.Offset(0, 1).Resize(1,
WorksheetFunction.Min(Application.RoundUp((end_date - start_date) / 7, 0),
256 - end_date.Row)) _
.Interior.ColorIndex = 8
Else
MsgBox "End date must be later than start date"
End If
Else
MsgBox "Enter a Start and End Date"
End If
End If
End If

End Sub

hth,

Doug Glancy