Thread: Conditional formating in Excel Charts.

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

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
donaldb@281.com
"REvans" <REvans@discussions.microsoft.com> wrote in message
news:81929CE4-B419-4AD4-8572-845B101E77A8@microsoft.com...
> 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

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

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
>

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
> >
>