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