Conditional formating in Excel Charts.

[REvans]

Can bar chart colors be tied to and change with a given criteria (a
conditional format for graphs)?

I am trying to color code variations in a waterfall chart (using Excel's Bar
Chart). I would like to code bars associated with positive impacts in green
and bars associated with negative inpacts (not negative numbers) in red.

Thanks

[Don Guillett]

You can incorporate this within an if statement and even make automatic by
putting in the worksheet_change event of the sheet code.

Sub seriescolor()
ActiveSheet.ChartObjects("chart 1") _
..Chart.SeriesCollection(1).Interior.ColorIndex = 3
End Sub

--
Don Guillett
SalesAid Software
[email protected]
"REvans" <[email protected]> wrote in message
news:[email protected]...
> Can bar chart colors be tied to and change with a given criteria (a
> conditional format for graphs)?
>
> I am trying to color code variations in a waterfall chart (using Excel's
Bar
> Chart). I would like to code bars associated with positive impacts in
green
> and bars associated with negative inpacts (not negative numbers) in red.
>
> Thanks

[REvans]

Thanks very much for the response, I will give it a shot

"REvans" wrote:

> Can bar chart colors be tied to and change with a given criteria (a
> conditional format for graphs)?
>
> I am trying to color code variations in a waterfall chart (using Excel's Bar
> Chart). I would like to code bars associated with positive impacts in green
> and bars associated with negative inpacts (not negative numbers) in red.
>
> Thanks

[Jon Peltier]

Alternatively, you could try this approach, with multiple series, which
does not rely on a macro being run to reflect changing values:

http://peltiertech.com/Excel/Charts/...nalChart1.html

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

Don Guillett wrote:

> You can incorporate this within an if statement and even make automatic by
> putting in the worksheet_change event of the sheet code.
>
> Sub seriescolor()
> ActiveSheet.ChartObjects("chart 1") _
> .Chart.SeriesCollection(1).Interior.ColorIndex = 3
> End Sub
>

[REvans]

Thank you for the response. My workaround is a bit cumbersome now. I am
using separate data series for positive transactions and negative
transactions (selected via conditional statements) coupled with ranking and
lookup routines. It gets to a point when I think crayons and a straightedge
provide the most direct solution.

It appears that you have a great deal of valuable information at your site.
Water-fall charts while a great tool to relay information, can be a pain.

Thank you again.

"Jon Peltier" wrote:

> Alternatively, you could try this approach, with multiple series, which
> does not rely on a macro being run to reflect changing values:
>
> http://peltiertech.com/Excel/Charts/...nalChart1.html
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Peltier Technical Services
> Tutorials and Custom Solutions
> http://PeltierTech.com/
> _______
>
> Don Guillett wrote:
>
> > You can incorporate this within an if statement and even make automatic by
> > putting in the worksheet_change event of the sheet code.
> >
> > Sub seriescolor()
> > ActiveSheet.ChartObjects("chart 1") _
> > .Chart.SeriesCollection(1).Interior.ColorIndex = 3
> > End Sub
> >
>