If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Conditional Formatting of Bar Charts
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? |
#2
|
|||
|
|||
Conditional Formatting of Bar Charts
Non-VBA:
Simple Conditional Charts http://peltiertech.com/Excel/Charts/...nalChart1.html VBA: VBA Conditional Formatting of Charts by Value http://peltiertech.com/WordPress/vba...arts-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? |
#3
|
|||
|
|||
Conditional Formatting of Bar Charts
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? |
Thread Tools | |
Display Modes | |
|
|