Hey Rick-

I am having a slight issue. When I put the coseinto a blank worksheet,
everything works fine. I have incorporated it into an already established
sheet that I have. The one difference is the placement of cells. My
start/stop dates are columns F & G. I made the adjustment in the cose as you
instructed. My months start in column Z so I tweaked the code...instead of
6, I put 26 (Const DataStartCol As Long = 26). My costs are in column C and
I made that adjustment. Lastly, my months start in row 2 and my data starts
in row 3. Made those changes. When I run the macro, it shades columns Z &
AA dark blue. Also, instead of "Sheet 4", I added the name of my worksheet.

On a dummy sheet, I have tried playing around moving things by a column or a
row and it still works fine. Does the fact that I am starting all the way
out in Column Z cause a problem.

If its easier, you can write to me at . I don't know if
thats considered taboo on these boards....whichever approach you're more
comfortable with. Thank you again.

Chris

"Rick Rothstein (MVP - VB)" wrote:

Thanks again for the quick response. Were you going to attach the full
revised code?

LOL... Well, yes, I had planned to, but I forgot. See the code after my
signature.

Also, for item #3.....there are some items where I may want to manually
insert the dollar values (contract might be front loaded for example). So
I
was thinking of having the task name in column B and having the option to
put
an "X" in column A if I wanted to either a) not show $ amounts or b) if I
wanted to manually type them in. I would then go and hide column A for
aesthetics. Does that make sense?

Yes, I understood what you had asked for, but I really think you will like
my idea. Give it a try and if you really don't like it, I'll revise the
code. To repeat it, my idea was simply to make the last character in the
Task Name field an asterisk (you can separate the asterisk from the Task
Name by a space or two if you think that will look better; the key is that
if the rightmost character is an asterisk, the distributed money values will
be suppressed). In thinking about it, perhaps attaching the asterisk to the
estimated cost would be a better place to put the asterisk (although if made
the trailing character, it would affect the alignment of the numbers in that
column). Anyway, try it with the trailing asterisk in the Task Name column
as see if you like it or not. By the way, you can change back and forth
(asterisk, no asterisk) easily enough, just rerun the GanttChart macro after
the change.

Rick

Sub GanttChart()
Dim x As Long
Dim Z As Long
Dim LastRow As Long
Dim LastCol As Long
Dim StartFinishDateCount As Long
Dim Start As Range
Dim Finish As Range
Dim DateSpan() As String
Const DateHeadersRow As Long = 1
Const DataStartRow As Long = 2
Const DataStartCol As Long = 6
Const EstimatedCostCol As String = "C"
Const StartDateCol As String = "D"
Const FinishDateCol As String = "E"
With Worksheets("Sheet4")
LastRow = .Cells(Rows.Count, StartDateCol).End(xlUp).Row
LastCol = .Cells(DateHeadersRow, Columns.Count).End(xlToLeft).Column
ReDim DateSpan(DataStartCol To LastCol)
For x = DataStartCol To LastCol
DateSpan(x) = Format(.Cells(1, x).Value, "mmm-yyyy")
Next
.Range(.Cells(DataStartRow, DataStartCol), _
.Cells(LastRow, LastCol)).Clear
For x = DataStartRow To LastRow
StartFinishDateCount = 0
For Z = DataStartCol To LastCol
If DateSpan(Z) = Format$(.Cells(x, StartDateCol). _
Value, "mmm-yyyy") Then
Set Start = Cells(x, Z)
StartFinishDateCount = StartFinishDateCount + 1
End If
If DateSpan(Z) = Format$(.Cells(x, FinishDateCol). _
Value, "mmm-yyyy") Then
Set Finish = .Cells(x, Z)
StartFinishDateCount = StartFinishDateCount + 1
End If
If StartFinishDateCount = 2 Then Exit For
Next
If .Cells(x, "A").Value Like "*[!*]" Then
For Z = Start.Column To Finish.Column
.Cells(x, Z).Value = Format(.Cells(x, EstimatedCostCol).Value / _
(Finish.Column - Start.Column + 1), "$#,###")
Next
End If
.Range(Start, Finish).Cells.Interior.Color = RGB(10, 5, 96)
.Range(Start, Finish).Cells.Font.Color = vbWhite
Next
End With
End Sub