Dynamically change bar chart segment colors...

[JohnWRS]

I need to present a bar chart with segments that represent periods o
time a policy was in force. The segments can have thre
conditions/colors as follows: Prohibited/Red
Permitted_under_Restrictions/Amber, Permitted/Green. The trick is tha
the colors can switch back and forth several times over a three yea
period. And, I would like to represent the segment lengths down to th
day. There are samples of GANTT charts that seem to have features lik
this, some are crude using conditional formating of cells, others ar
sophisticated using VBA. Any suggestions on how to attack this?
Thanks, Joh

 

[Jon Peltier]

John -

I recently had a similar project in which I had more colors than
conditional formatting allows, but it was too complex for a gantt type
floating bar chart. So I used the worksheet, had a formula in each cell
that showed 1, 2, etc for the color code, then used VBA to recolor the
cells (and kept the formula result hidden). For the faster computers
running the program, I hitched it to a Worksheet_Change procedure,
otherwise to a menu item. With only three conditions, you can use
conditional formatting, and it is instantaneous.

The apparent crudeness of a worksheet based solution is remedied by
formatting and appropriate labeling.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______