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