Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Does anyone have a template for using Excel as a simple Construct.
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Does anyone have a template for using Excel as a simple Construct.
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_dat e - 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 "JLWCFM" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I need a simple excel-based template for business accounts | Excel Discussion (Misc queries) | |||
how do i construct a correlogram in excel? | Charts and Charting in Excel | |||
How can I construct a broken Y axis in Excel? | Excel Discussion (Misc queries) | |||
Simple Template excel for Books | Excel Discussion (Misc queries) | |||
Classes, and the For Each construct in Excel? | Excel Programming |