Help!

Conditional Formatting of Bar Charts

 
Post new topic   General Reply to Topic (not reply to a specific post)    Forums Home -> Charting RSS
Next:  2002 MSWord photojournal pictures showing in web ..  
Author Message
septimus
External


Since: Nov 05, 2009
Posts: 2



PostPosted: Thu Nov 05, 2009 4:19 pm    Post subject: Conditional Formatting of Bar Charts
Archived from groups: microsoft>public>excel>charting (more info?)

I've got a bar chart in an Excel 2007 worksheet that needs to have
bars colored conditionally. I can do this manually -- by clicking on
the Chart Format ribbon, then clicking on the bar, and then clicking
the "Fill Color" icon on the ribbon.

But surely there's a way to automate this?! The Conditional Formatting
feature is not available for charts. When I try to use the Macro
Recorder, this step simply doesn't get recorded.

I've spent a great deal of time with VBA, trying to find a chart bar
object of some kind with a Fill.BackColor property. I can't find one.
There's no "Shape" object within the Chart object or the ChartObjects
collection or any other such thing.

The closest I've gotten is the ClearFormats method of the
SeriesCollection.Point object, which does make the chart bar
transparent. But the Point object's Fill property is read only!

Help! Has anybody found a way to automate conditional color formatting
for bar chart bars?
Back to top
Jon Peltier
External


Since: Feb 01, 2004
Posts: 194



PostPosted: Thu Nov 05, 2009 8:04 pm    Post subject: Re: Conditional Formatting of Bar Charts [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

Non-VBA:
Simple Conditional Charts
http://peltiertech.com/Excel/Charts/ConditionalChart1.html

VBA:
VBA Conditional Formatting of Charts by Value
http://peltiertech.com/WordPress/vba-conditional-formatting-of-charts-by-value/

Try the non-VBA technique first.

- Jon
-------
Jon Peltier
Peltier Technical Services, Inc.
http://peltiertech.com/



septimus wrote:
> I've got a bar chart in an Excel 2007 worksheet that needs to have
> bars colored conditionally. I can do this manually -- by clicking on
> the Chart Format ribbon, then clicking on the bar, and then clicking
> the "Fill Color" icon on the ribbon.
>
> But surely there's a way to automate this?! The Conditional Formatting
> feature is not available for charts. When I try to use the Macro
> Recorder, this step simply doesn't get recorded.
>
> I've spent a great deal of time with VBA, trying to find a chart bar
> object of some kind with a Fill.BackColor property. I can't find one.
> There's no "Shape" object within the Chart object or the ChartObjects
> collection or any other such thing.
>
> The closest I've gotten is the ClearFormats method of the
> SeriesCollection.Point object, which does make the chart bar
> transparent. But the Point object's Fill property is read only!
>
> Help! Has anybody found a way to automate conditional color formatting
> for bar chart bars?
Back to top
septimus
External


Since: Nov 05, 2009
Posts: 2



PostPosted: Thu Nov 05, 2009 11:35 pm    Post subject: Re: Conditional Formatting of Bar Charts [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

Eureka! Thanks, Jon.


On Nov 5, 2:04 pm, Jon Peltier wrote:
> Non-VBA:
> Simple Conditional Chartshttp://peltiertech.com/Excel/Charts/ConditionalChart1.html
>
> VBA:
> VBA Conditional Formatting of Charts by Valuehttp://peltiertech.com/WordPress/vba-conditional-formatting-of-charts...
>
> Try the non-VBA technique first.
>
> - Jon
> -------
> Jon Peltier
> Peltier Technical Services, Inc.http://peltiertech.com/
>
> septimus wrote:
> > I've got a bar chart in an Excel 2007 worksheet that needs to have
> > bars colored conditionally. I can do this manually -- by clicking on
> > the Chart Format ribbon, then clicking on the bar, and then clicking
> > the "Fill Color" icon on the ribbon.
>
> > But surely there's a way to automate this?! The Conditional Formatting
> > feature is not available for charts. When I try to use the Macro
> > Recorder, this step simply doesn't get recorded.
>
> > I've spent a great deal of time with VBA, trying to find a chart bar
> > object of some kind with a Fill.BackColor property. I can't find one.
> > There's no "Shape" object within the Chart object or the ChartObjects
> > collection or any other such thing.
>
> > The closest I've gotten is the ClearFormats method of the
> > SeriesCollection.Point object, which does make the chart bar
> > transparent. But the Point object's Fill property is read only!
>
> > Help! Has anybody found a way to automate conditional color formatting
> > for bar chart bars?
Back to top
Display posts from previous:   
Post new topic   General Reply to Topic (not reply to a specific post)    Forums Home -> Charting All times are: Eastern Time (US & Canada)
Page 1 of 1

 
You can post new topics in this forum
You can reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum