Hi. I am trying to mimic some MS Project functionality in Excel. I want to
try and automate the creation of a basic gantt chart. Is there a way I can
do the following:

Columns D - O are labeled months of the year. Column A is a task, column B
is a start month and column C is a stop month. I would like to set it up
such that once the user puts in a start/stop month, that the respective cells
(and everything in between) gets shaded a certain color.

So for example if I have a task of develop software in row 2 and the start
stop is Feb/Apr, I would like cells E2, F2 and G2 to be shaded black.

Thanks in advance.

Chris

Here is some code to do that...

Sub GanttChart()
Dim X As Long
Dim LastRow As Long
Dim Start As Range
Dim Finish As Range
With Worksheets("Sheet3")
Range("D2:O999").ClearFormats
LastRow = .Cells(Rows.Count, 1).End(xlUp).Row
For X = 2 To LastRow
Set Start = Cells(X, 3 + Month(.Range("B" & X).Value & " 1, 2000"))
Set Finish = Cells(X, 3 + Month(.Range("C" & X).Value & " 1, 2000"))
Range(Start, Finish).Cells.Interior.Color = RGB(172, 172, 172)
Next
End With
End Sub

I used a gray color instead of black, but you can change the color on a per
row basis via the RGB function call if you want. Also, the code works based
on 3-letter month abbreviations in Columns B and C as well as in your
headers; if you want something different, let me know and I'll modify the
code to account for it.

Rick


"CMD" wrote in message
...
Hi. I am trying to mimic some MS Project functionality in Excel. I want
to
try and automate the creation of a basic gantt chart. Is there a way I
can
do the following:

Columns D - O are labeled months of the year. Column A is a task, column
B
is a start month and column C is a stop month. I would like to set it up
such that once the user puts in a start/stop month, that the respective
cells
(and everything in between) gets shaded a certain color.

So for example if I have a task of develop software in row 2 and the start
stop is Feb/Apr, I would like cells E2, F2 and G2 to be shaded black.

Thanks in advance.

Chris

For Cell Shading, take a look at

Workbooks("Book1").Worksheets("Sheet1").Range(Cell s(2,5).Address(,,xlA1,False),Cells(2,7).Address(,, xlA1,False)).Interior.ColorIndex
= 1

For a list of the different colors using the ColorIndex, you may go to:

http://www.mvps.org/dmcritchie/excel/colors.htm

--
Thanks,

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
"CMD" wrote in message
...
Hi. I am trying to mimic some MS Project functionality in Excel. I want
to
try and automate the creation of a basic gantt chart. Is there a way I
can
do the following:

Columns D - O are labeled months of the year. Column A is a task, column
B
is a start month and column C is a stop month. I would like to set it up
such that once the user puts in a start/stop month, that the respective
cells
(and everything in between) gets shaded a certain color.

So for example if I have a task of develop software in row 2 and the start
stop is Feb/Apr, I would like cells E2, F2 and G2 to be shaded black.

Thanks in advance.

Chris

Thanks, Rick! Would be great if I could insert MMM-YY format. Also, I am not
super experienced with macros. Do I just cut/paste your macro into a blank
macro that i create? I set up my sheet as I described in my first post. I
inserted an object, right clicked, said create macro, went into edit and
pasted your macro. It gives me an error when I run it. Sorry for the dumb
questions.

Chris

"Rick Rothstein (MVP - VB)" wrote:

Here is some code to do that...

Sub GanttChart()
Dim X As Long
Dim LastRow As Long
Dim Start As Range
Dim Finish As Range
With Worksheets("Sheet3")
Range("D2:O999").ClearFormats
LastRow = .Cells(Rows.Count, 1).End(xlUp).Row
For X = 2 To LastRow
Set Start = Cells(X, 3 + Month(.Range("B" & X).Value & " 1, 2000"))
Set Finish = Cells(X, 3 + Month(.Range("C" & X).Value & " 1, 2000"))
Range(Start, Finish).Cells.Interior.Color = RGB(172, 172, 172)
Next
End With
End Sub

I used a gray color instead of black, but you can change the color on a per
row basis via the RGB function call if you want. Also, the code works based
on 3-letter month abbreviations in Columns B and C as well as in your
headers; if you want something different, let me know and I'll modify the
code to account for it.

Rick


"CMD" wrote in message
...
Hi. I am trying to mimic some MS Project functionality in Excel. I want
to
try and automate the creation of a basic gantt chart. Is there a way I
can
do the following:

Columns D - O are labeled months of the year. Column A is a task, column
B
is a start month and column C is a stop month. I would like to set it up
such that once the user puts in a start/stop month, that the respective
cells
(and everything in between) gets shaded a certain color.

So for example if I have a task of develop software in row 2 and the start
stop is Feb/Apr, I would like cells E2, F2 and G2 to be shaded black.

Thanks in advance.

Chris

Hi Rick-
I think I figured it out and now have it working. I really appreciate you
taking the time. Can I add a layer of complexity? (1) If its possible to
change the date format to MMM-YY that would be great. (2) Is it possible for
me to add a 4th column up front and include a dollar amount and if a dollar
amount exists, have it equally distributed over the shaded cells. Thanks
again so much for your help.
Chris

"Rick Rothstein (MVP - VB)" wrote:

Here is some code to do that...

Sub GanttChart()
Dim X As Long
Dim LastRow As Long
Dim Start As Range
Dim Finish As Range
With Worksheets("Sheet3")
Range("D2:O999").ClearFormats
LastRow = .Cells(Rows.Count, 1).End(xlUp).Row
For X = 2 To LastRow
Set Start = Cells(X, 3 + Month(.Range("B" & X).Value & " 1, 2000"))
Set Finish = Cells(X, 3 + Month(.Range("C" & X).Value & " 1, 2000"))
Range(Start, Finish).Cells.Interior.Color = RGB(172, 172, 172)
Next
End With
End Sub

I used a gray color instead of black, but you can change the color on a per
row basis via the RGB function call if you want. Also, the code works based
on 3-letter month abbreviations in Columns B and C as well as in your
headers; if you want something different, let me know and I'll modify the
code to account for it.

Rick


"CMD" wrote in message
...
Hi. I am trying to mimic some MS Project functionality in Excel. I want
to
try and automate the creation of a basic gantt chart. Is there a way I
can
do the following:

Columns D - O are labeled months of the year. Column A is a task, column
B
is a start month and column C is a stop month. I would like to set it up
such that once the user puts in a start/stop month, that the respective
cells
(and everything in between) gets shaded a certain color.

So for example if I have a task of develop software in row 2 and the start
stop is Feb/Apr, I would like cells E2, F2 and G2 to be shaded black.

Thanks in advance.

Chris

Alright, I think we might need some clarification. You want to include the
year with your start and finish dates which sort of indicates the chart can
span across at least two years worth of time; however, your initial post
said you only had the 12 month (abbreviated) names in Columns D thru O
indicating a span of 12-months within a single year. If you have simplified
your question in order to post it to this newsgroup, you shouldn't do
that... very often the coding to use is dictated by the conditions it will
execute over. Anyway, below is code modified to still work within the same
calendar year... it requires your start and finish dates to be real dates
(that is what should be happening when you enter something like Mar-2008...
if you enter it as Mar-08, I think it will become March 8th of the current
year) and still assumes your headers contain the abbreviated month names
within that year (by the way, as written, the code below doesn't use the
year at all)...

Sub GanttChart()
Dim X As Long
Dim LastRow As Long
Dim Start As Range
Dim Finish As Range
With Worksheets("Sheet3")
Range("D2:O999").ClearFormats
LastRow = .Cells(Rows.Count, 1).End(xlUp).Row
For X = 2 To LastRow
Set Start = Cells(X, 3 + Month(.Range("B" & X).Value))
Set Finish = Cells(X, 3 + Month(.Range("C" & X).Value))
Range(Start, Finish).Cells.Interior.Color = RGB(172, 172, 172)
Next
End With
End Sub

To implement this code, go to the worksheet with your Gantt Chart layout on
it, right-click the worksheet tab at the bottom of the page, and copy/paste
the above code into the window that appeared. Now, go back to your worksheet
and enter some start and end dates in Columns B and C. When you are done,
press Alt+F8 and select GanttChart from the list and click the RUN button.
If all went correctly, you should see your date ranges get highlighted.

Rick


"CMD" wrote in message
...
Thanks, Rick! Would be great if I could insert MMM-YY format. Also, I am
not
super experienced with macros. Do I just cut/paste your macro into a
blank
macro that i create? I set up my sheet as I described in my first post.
I
inserted an object, right clicked, said create macro, went into edit and
pasted your macro. It gives me an error when I run it. Sorry for the
dumb
questions.

Chris

"Rick Rothstein (MVP - VB)" wrote:

Here is some code to do that...

Sub GanttChart()
Dim X As Long
Dim LastRow As Long
Dim Start As Range
Dim Finish As Range
With Worksheets("Sheet3")
Range("D2:O999").ClearFormats
LastRow = .Cells(Rows.Count, 1).End(xlUp).Row
For X = 2 To LastRow
Set Start = Cells(X, 3 + Month(.Range("B" & X).Value & " 1, 2000"))
Set Finish = Cells(X, 3 + Month(.Range("C" & X).Value & " 1,
2000"))
Range(Start, Finish).Cells.Interior.Color = RGB(172, 172, 172)
Next
End With
End Sub

I used a gray color instead of black, but you can change the color on a
per
row basis via the RGB function call if you want. Also, the code works
based
on 3-letter month abbreviations in Columns B and C as well as in your
headers; if you want something different, let me know and I'll modify the
code to account for it.

Rick


"CMD" wrote in message
...
Hi. I am trying to mimic some MS Project functionality in Excel. I
want
to
try and automate the creation of a basic gantt chart. Is there a way I
can
do the following:

Columns D - O are labeled months of the year. Column A is a task,
column
B
is a start month and column C is a stop month. I would like to set it
up
such that once the user puts in a start/stop month, that the respective
cells
(and everything in between) gets shaded a certain color.

So for example if I have a task of develop software in row 2 and the
start
stop is Feb/Apr, I would like cells E2, F2 and G2 to be shaded black.

Thanks in advance.

Chris

See my other response to your other message for the answer to #1. I'm not
sure I understand what you want for #2. Are you asking to fill in numbers
into each cell that is shaded? If so, are you asking to have the dollar
value divided by the number of months, this being the same value, placed in
each cell? Or did you want a cumulative dollar amount entered into each
cell?

Rick


"CMD" wrote in message
...
Hi Rick-
I think I figured it out and now have it working. I really appreciate you
taking the time. Can I add a layer of complexity? (1) If its possible to
change the date format to MMM-YY that would be great. (2) Is it possible
for
me to add a 4th column up front and include a dollar amount and if a
dollar
amount exists, have it equally distributed over the shaded cells. Thanks
again so much for your help.
Chris

"Rick Rothstein (MVP - VB)" wrote:

Here is some code to do that...

Sub GanttChart()
Dim X As Long
Dim LastRow As Long
Dim Start As Range
Dim Finish As Range
With Worksheets("Sheet3")
Range("D2:O999").ClearFormats
LastRow = .Cells(Rows.Count, 1).End(xlUp).Row
For X = 2 To LastRow
Set Start = Cells(X, 3 + Month(.Range("B" & X).Value & " 1, 2000"))
Set Finish = Cells(X, 3 + Month(.Range("C" & X).Value & " 1,
2000"))
Range(Start, Finish).Cells.Interior.Color = RGB(172, 172, 172)
Next
End With
End Sub

I used a gray color instead of black, but you can change the color on a
per
row basis via the RGB function call if you want. Also, the code works
based
on 3-letter month abbreviations in Columns B and C as well as in your
headers; if you want something different, let me know and I'll modify the
code to account for it.

Rick


"CMD" wrote in message
...
Hi. I am trying to mimic some MS Project functionality in Excel. I
want
to
try and automate the creation of a basic gantt chart. Is there a way I
can
do the following:

Columns D - O are labeled months of the year. Column A is a task,
column
B
is a start month and column C is a stop month. I would like to set it
up
such that once the user puts in a start/stop month, that the respective
cells
(and everything in between) gets shaded a certain color.

So for example if I have a task of develop software in row 2 and the
start
stop is Feb/Apr, I would like cells E2, F2 and G2 to be shaded black.

Thanks in advance.

Chris

Hi Rick-
Thanks again for your help....and patience. Yes, I did simplify the example
in my original post. I would like to use this to span multiple years. Also,
for #2, I would like to divide the dollar amount up between the months. So,
if I have $20K for an activity that spans Jan-Apr, I want to have $5K each in
the shaded cells of Jan, Feb, Mar, Apr.

Chris

"Rick Rothstein (MVP - VB)" wrote:

See my other response to your other message for the answer to #1. I'm not
sure I understand what you want for #2. Are you asking to fill in numbers
into each cell that is shaded? If so, are you asking to have the dollar
value divided by the number of months, this being the same value, placed in
each cell? Or did you want a cumulative dollar amount entered into each
cell?

Rick


"CMD" wrote in message
...
Hi Rick-
I think I figured it out and now have it working. I really appreciate you
taking the time. Can I add a layer of complexity? (1) If its possible to
change the date format to MMM-YY that would be great. (2) Is it possible
for
me to add a 4th column up front and include a dollar amount and if a
dollar
amount exists, have it equally distributed over the shaded cells. Thanks
again so much for your help.
Chris

"Rick Rothstein (MVP - VB)" wrote:

Here is some code to do that...

Sub GanttChart()
Dim X As Long
Dim LastRow As Long
Dim Start As Range
Dim Finish As Range
With Worksheets("Sheet3")
Range("D2:O999").ClearFormats
LastRow = .Cells(Rows.Count, 1).End(xlUp).Row
For X = 2 To LastRow
Set Start = Cells(X, 3 + Month(.Range("B" & X).Value & " 1, 2000"))
Set Finish = Cells(X, 3 + Month(.Range("C" & X).Value & " 1,
2000"))
Range(Start, Finish).Cells.Interior.Color = RGB(172, 172, 172)
Next
End With
End Sub

I used a gray color instead of black, but you can change the color on a
per
row basis via the RGB function call if you want. Also, the code works
based
on 3-letter month abbreviations in Columns B and C as well as in your
headers; if you want something different, let me know and I'll modify the
code to account for it.

Rick


"CMD" wrote in message
...
Hi. I am trying to mimic some MS Project functionality in Excel. I
want
to
try and automate the creation of a basic gantt chart. Is there a way I
can
do the following:

Columns D - O are labeled months of the year. Column A is a task,
column
B
is a start month and column C is a stop month. I would like to set it
up
such that once the user puts in a start/stop month, that the respective
cells
(and everything in between) gets shaded a certain color.

So for example if I have a task of develop software in row 2 and the
start
stop is Feb/Apr, I would like cells E2, F2 and G2 to be shaded black.

Thanks in advance.

Chris

Give the code below a try. It assumes the estimated costs are in Column C,
the start date in Column D, the finish date in Column E. Also, starting in
Column F, Row 1 are the date headers which must be real Excel dates (you can
use an entry like Feb-2008, which will default to the first of the month, or
enter a full date, and then format them all to your desired MMM-YY format).
Also, the start and finish dates need to be real dates also, but the can be
any day of the month (in case you want to be able to see exactly which dates
are the estimated start and finish dates within your chart). The estimated
costs, will be distributed throughout the months, but without regard to the
actual day within the month the activity starts or ends. So, if the
estimated cost is $3000 and the date span is from January 31, 2008 to March
1, 2008, that will be regarded as 3 month (which is the number of months
that will be shaded) and each of those months will have one-third of the
estimated cost ($1000 each) shown in them. Hopefully, this all meets with
your approval.

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("Sheet1")
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
For Z = Start.Column To Finish.Column
Cells(X, Z).Value = Cells(X, EstimatedCostCol).Value /
(Finish.Column - Start.Column + 1)
Next
Range(Start, Finish).Cells.Interior.Color = RGB(172, 172, 172)
Next
End With
End Sub

Rick


"CMD" wrote in message
...
Hi Rick-
Thanks again for your help....and patience. Yes, I did simplify the
example
in my original post. I would like to use this to span multiple years.
Also,
for #2, I would like to divide the dollar amount up between the months.
So,
if I have $20K for an activity that spans Jan-Apr, I want to have $5K each
in
the shaded cells of Jan, Feb, Mar, Apr.

Chris

"Rick Rothstein (MVP - VB)" wrote:

See my other response to your other message for the answer to #1. I'm not
sure I understand what you want for #2. Are you asking to fill in numbers
into each cell that is shaded? If so, are you asking to have the dollar
value divided by the number of months, this being the same value, placed
in
each cell? Or did you want a cumulative dollar amount entered into each
cell?

Rick


"CMD" wrote in message
...
Hi Rick-
I think I figured it out and now have it working. I really appreciate
you
taking the time. Can I add a layer of complexity? (1) If its possible
to
change the date format to MMM-YY that would be great. (2) Is it
possible
for
me to add a 4th column up front and include a dollar amount and if a
dollar
amount exists, have it equally distributed over the shaded cells.
Thanks
again so much for your help.
Chris

"Rick Rothstein (MVP - VB)" wrote:

Here is some code to do that...

Sub GanttChart()
Dim X As Long
Dim LastRow As Long
Dim Start As Range
Dim Finish As Range
With Worksheets("Sheet3")
Range("D2:O999").ClearFormats
LastRow = .Cells(Rows.Count, 1).End(xlUp).Row
For X = 2 To LastRow
Set Start = Cells(X, 3 + Month(.Range("B" & X).Value & " 1,
2000"))
Set Finish = Cells(X, 3 + Month(.Range("C" & X).Value & " 1,
2000"))
Range(Start, Finish).Cells.Interior.Color = RGB(172, 172, 172)
Next
End With
End Sub

I used a gray color instead of black, but you can change the color on
a
per
row basis via the RGB function call if you want. Also, the code works
based
on 3-letter month abbreviations in Columns B and C as well as in your
headers; if you want something different, let me know and I'll modify
the
code to account for it.

Rick


"CMD" wrote in message
...
Hi. I am trying to mimic some MS Project functionality in Excel. I
want
to
try and automate the creation of a basic gantt chart. Is there a
way I
can
do the following:

Columns D - O are labeled months of the year. Column A is a task,
column
B
is a start month and column C is a stop month. I would like to set
it
up
such that once the user puts in a start/stop month, that the
respective
cells
(and everything in between) gets shaded a certain color.

So for example if I have a task of develop software in row 2 and the
start
stop is Feb/Apr, I would like cells E2, F2 and G2 to be shaded
black.

Thanks in advance.

Chris

Sorry, I forgot to put in the line continuations in order to avoid
newsreader line wrapping issues. Here is the code with the line continuation
accounted for...

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("Sheet1")
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
For Z = Start.Column To Finish.Column
Cells(X, Z).Value = Cells(X, EstimatedCostCol).Value / _
(Finish.Column - Start.Column + 1)
Next
Range(Start, Finish).Cells.Interior.Color = RGB(172, 172, 172)
Next
End With
End Sub

Rick


"Rick Rothstein (MVP - VB)" wrote in
message ...
Give the code below a try. It assumes the estimated costs are in Column C,
the start date in Column D, the finish date in Column E. Also, starting in
Column F, Row 1 are the date headers which must be real Excel dates (you
can use an entry like Feb-2008, which will default to the first of the
month, or enter a full date, and then format them all to your desired
MMM-YY format). Also, the start and finish dates need to be real dates
also, but the can be any day of the month (in case you want to be able to
see exactly which dates are the estimated start and finish dates within
your chart). The estimated costs, will be distributed throughout the
months, but without regard to the actual day within the month the activity
starts or ends. So, if the estimated cost is $3000 and the date span is
from January 31, 2008 to March 1, 2008, that will be regarded as 3 month
(which is the number of months that will be shaded) and each of those
months will have one-third of the estimated cost ($1000 each) shown in
them. Hopefully, this all meets with your approval.

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("Sheet1")
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
For Z = Start.Column To Finish.Column
Cells(X, Z).Value = Cells(X, EstimatedCostCol).Value /
(Finish.Column - Start.Column + 1)
Next
Range(Start, Finish).Cells.Interior.Color = RGB(172, 172, 172)
Next
End With
End Sub

Rick


"CMD" wrote in message
...
Hi Rick-
Thanks again for your help....and patience. Yes, I did simplify the
example
in my original post. I would like to use this to span multiple years.
Also,
for #2, I would like to divide the dollar amount up between the months.
So,
if I have $20K for an activity that spans Jan-Apr, I want to have $5K
each in
the shaded cells of Jan, Feb, Mar, Apr.

Chris

"Rick Rothstein (MVP - VB)" wrote:

See my other response to your other message for the answer to #1. I'm
not
sure I understand what you want for #2. Are you asking to fill in
numbers
into each cell that is shaded? If so, are you asking to have the dollar
value divided by the number of months, this being the same value, placed
in
each cell? Or did you want a cumulative dollar amount entered into each
cell?

Rick


"CMD" wrote in message
...
Hi Rick-
I think I figured it out and now have it working. I really appreciate
you
taking the time. Can I add a layer of complexity? (1) If its
possible to
change the date format to MMM-YY that would be great. (2) Is it
possible
for
me to add a 4th column up front and include a dollar amount and if a
dollar
amount exists, have it equally distributed over the shaded cells.
Thanks
again so much for your help.
Chris

"Rick Rothstein (MVP - VB)" wrote:

Here is some code to do that...

Sub GanttChart()
Dim X As Long
Dim LastRow As Long
Dim Start As Range
Dim Finish As Range
With Worksheets("Sheet3")
Range("D2:O999").ClearFormats
LastRow = .Cells(Rows.Count, 1).End(xlUp).Row
For X = 2 To LastRow
Set Start = Cells(X, 3 + Month(.Range("B" & X).Value & " 1,
2000"))
Set Finish = Cells(X, 3 + Month(.Range("C" & X).Value & " 1,
2000"))
Range(Start, Finish).Cells.Interior.Color = RGB(172, 172, 172)
Next
End With
End Sub

I used a gray color instead of black, but you can change the color on
a
per
row basis via the RGB function call if you want. Also, the code works
based
on 3-letter month abbreviations in Columns B and C as well as in your
headers; if you want something different, let me know and I'll modify
the
code to account for it.

Rick


"CMD" wrote in message
...
Hi. I am trying to mimic some MS Project functionality in Excel.
I
want
to
try and automate the creation of a basic gantt chart. Is there a
way I
can
do the following:

Columns D - O are labeled months of the year. Column A is a task,
column
B
is a start month and column C is a stop month. I would like to
set it
up
such that once the user puts in a start/stop month, that the
respective
cells
(and everything in between) gets shaded a certain color.

So for example if I have a task of develop software in row 2 and
the
start
stop is Feb/Apr, I would like cells E2, F2 and G2 to be shaded
black.

Thanks in advance.

Chris

I was away on a business trip for the past week. Thanks again for your
help. The macro works great. I hope I'm not going to the well too often
but
I have a couple (hopefully minor) requests. Can you set it up to do the
following:

1) Use a dark, royal blue for the shading instead of gray.
2) Make the cells formatted to US currency (no decimal places)
3) This one might be tougher....can it be set up such that if I place an
"x"
in column A that it will not spread out any dollar amounts (this will be
useful if a) there are no dollars associated with the activity or b) I
want
to manually assign the dollars in a nonlinear fashion.
4) Lastly, is it easy for me to adjust which column things are in? Is
there
a specific line in the cose that I can change ...for example, if start
date
is in column D and I want to moves things to the right and make it column
F?

Again, I can't tell you how helpful this is. My colleagues love it.

I'm glad you and your colleagues like how the code performs. Now, for your
questions...

1) I only guessed at what "dark, royal blue" might be. You can play around
with the color setting statement yourself until you find a color you like.
The 5th line of code up from the bottom is the statement you need to play
with. The RGB function is assigning amounts (from 0 to 255 each) to mix of
Red, Green and Blue to produce a final color... just change these values and
see if it looks anywhere near the color you are thinking about. Notice also
that the next line after (which I added in this modification) makes the font
color white so that it will stand out against a dark color better.

2) Done

3) I applied my personal preference to this one and instead of a "X" in
column "A" (I presume in front of the task name itself), I made it that if
the last character in the task name in Column A is an asterisk (the asterisk
can follow the task name with one or more spaces in between them if
desired), then no dollar apportionment would be shown (but the font color
will still be white when you type in your own values). I thought doing it
this way would look nicer. Is that okay? If not, change this statement...

If .Cells(X, "A").Value Like "*[!*]" Then

to this statement instead...

If .Cells(X, "A").Value Like "[!xX]*" Then

and it will suppress the money apportionment when the first character of the
task name is either a lower or upper case "x".

4) There are 6 Const statements near the beginning of the code that control
where the code will look for certain items. Repeated here are those Const
statements

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"

The first one tells the code which row your chart's header "month" dates are
in (these are your multi-year columns where the shaded bars are placed). The
second one tells the code which row contains your first piece of data (the
tasknames and their associtated items). The third one tells the code which
column is the first of the multi-year colums where the shaded bars are
placed. The fourth one tells the code which column contains your overall
estimated cost for each task. The next to last one tells the code which
column contains the start dates and the last one is tells the code which
column contains the finish dates. Just change these as necessary and the
rest of the code should adjust around them accordingly.

Rick

Hey Rick-

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

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?


Chris

"Rick Rothstein (MVP - VB)" wrote:

I was away on a business trip for the past week. Thanks again for your
help. The macro works great. I hope I'm not going to the well too often
but
I have a couple (hopefully minor) requests. Can you set it up to do the
following:

1) Use a dark, royal blue for the shading instead of gray.
2) Make the cells formatted to US currency (no decimal places)
3) This one might be tougher....can it be set up such that if I place an
"x"
in column A that it will not spread out any dollar amounts (this will be
useful if a) there are no dollars associated with the activity or b) I
want
to manually assign the dollars in a nonlinear fashion.
4) Lastly, is it easy for me to adjust which column things are in? Is
there
a specific line in the cose that I can change ...for example, if start
date
is in column D and I want to moves things to the right and make it column
F?

Again, I can't tell you how helpful this is. My colleagues love it.

I'm glad you and your colleagues like how the code performs. Now, for your
questions...

1) I only guessed at what "dark, royal blue" might be. You can play around
with the color setting statement yourself until you find a color you like.
The 5th line of code up from the bottom is the statement you need to play
with. The RGB function is assigning amounts (from 0 to 255 each) to mix of
Red, Green and Blue to produce a final color... just change these values and
see if it looks anywhere near the color you are thinking about. Notice also
that the next line after (which I added in this modification) makes the font
color white so that it will stand out against a dark color better.

2) Done

3) I applied my personal preference to this one and instead of a "X" in
column "A" (I presume in front of the task name itself), I made it that if
the last character in the task name in Column A is an asterisk (the asterisk
can follow the task name with one or more spaces in between them if
desired), then no dollar apportionment would be shown (but the font color
will still be white when you type in your own values). I thought doing it
this way would look nicer. Is that okay? If not, change this statement...

If .Cells(X, "A").Value Like "*[!*]" Then

to this statement instead...

If .Cells(X, "A").Value Like "[!xX]*" Then

and it will suppress the money apportionment when the first character of the
task name is either a lower or upper case "x".

4) There are 6 Const statements near the beginning of the code that control
where the code will look for certain items. Repeated here are those Const
statements

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"

The first one tells the code which row your chart's header "month" dates are
in (these are your multi-year columns where the shaded bars are placed). The
second one tells the code which row contains your first piece of data (the
tasknames and their associtated items). The third one tells the code which
column is the first of the multi-year colums where the shaded bars are
placed. The fourth one tells the code which column contains your overall
estimated cost for each task. The next to last one tells the code which
column contains the start dates and the last one is tells the code which
column contains the finish dates. Just change these as necessary and the
rest of the code should adjust around them accordingly.

Rick

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

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