Conditional Formatting of Charts

[John Stanton]

Is there anyway that I can conditionally format a chart?
What I need to be able to do is set parameters for the
colour of a data series. For example I have a chart
showing total daily sales plotted on a day by day basis. I
want to be able to set the colour for each days sales
depending on the amount i.e. if it is over 500, then it
will be green, between 350 and 499 amber, and anything
below 350 will be red. I know this can be done on
individual cells, but can it be done in a chart?

All help greatly appreciated.

John

 

[Jon Peltier]

John -

I have a couple approaches on my Chart Formatting page:

http://www.geocities.com/jonpeltier/Excel/Charts/format.html

- Jon

 

[Guest]

I don't know of any way to do this in the chart itself.
However, if you try setting up your data range with 3
different series where the cells are only populated if the
fall within the particular value ranges (using if
statements) you can have 3 different series with your
specified colors. In a sense you are creating a
conditional data range for your chart.

 

[Jon Peltier]

Essentially, that's how my example works.

You can do it to the actual points on a chart by running a macro. The
macro evaluates each point, and applies the appropriate formatting. It
might seem better to use this approach to reduce the number of series in
the chart, but Excel has been designed to handle plenty of series. The
time the macro takes to evaluate a few series with a couple hundred
points will drive you to distraction.

- Jon

 

[John Stanton]

It works great - many thanks!

John

-----Original Message-----
John -

I have a couple approaches on my Chart Formatting page:

http://www.geocities.com/jonpeltier/Excel/Charts/format.htm
l

- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______



.