Conditional formating in Excel Charts.

#1 01-25-2005, 01:06 PM

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

#2 01-25-2005, 02:06 PM

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

#3 01-25-2005, 02:06 PM

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

#4 01-26-2005, 10:07 PM

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
>

#5 01-27-2005, 08:06 AM

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

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)