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?
0
septimus
11/5/2009 7:19:45 PM
excel.charting 18370 articles. 0 followers. Follow

2 Replies
1214 Views

Similar Articles

[PageSpeed] 51

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?
0
Jon
11/5/2009 8:04:47 PM
Eureka! Thanks, Jon.


On Nov 5, 2:04=A0pm, Jon Peltier <jo...@SPAMpeltiertech.com> wrote:
> Non-VBA:
> Simple Conditional Chartshttp://peltiertech.com/Excel/Charts/ConditionalC=
hart1.html
>
> VBA:
> VBA Conditional Formatting of Charts by Valuehttp://peltiertech.com/WordP=
ress/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?

0
septimus
11/6/2009 2:35:16 AM
Reply:

Similar Artilces:

Acces to Excel automation : chart problem
Good afternoon, This is my first post so apologies if i don't observe any etiquette. I'm coding Access to Excel to create an xls file with a sheet of figures and a chart embedded on the sheet. I've got most everything going fine BUT I can't get the X Category Axis set up on the graph. Any suggestions will be most appreciated. Here's the code: 'Create the first Chart - month to month figures Dim chart1 As ChartObject Set chart1 = xlSheet.ChartObjects.Add(10, 200, 920, 300) chart1.Chart.chartwizard _ Source:=xlSheet.Range("D3:e14"), _ ...

Changing the Outlook Bar
Using Outlook 2002 on a WinXP system. I want to add to the "other" category on my outlook bar a link to Word. Can't figure out how to do it. When I follow the directions given in the help file, I can only seem to be able to link to a folder, not a file within that folder. Is there a way to do this? Thanks. -- ----------------------------------------------------- Jeffrey Needle jeff.needle@gmail.com Try making a shortcut to Word.exe and putting it on your desktop. Then = drag it from your desktop into the Outlook Bar.=20 --=20 Sue Mosher, Outlook MVP Author of Co...

how to make the axis of charts to start at my lowest value(data)?
actually, i am making a chart with values starting at 40 to 85. when i plot the chart, it is located somewhere in the middle upto the end because excel tries to plot 0 to 40 on my x axis which i do not want. what i want is that the x-axis of my chart should start at 40 then probably by interval of five upto 85. also my y-axis to start at 140 upto ~180. here is my data. x y 72 160 54 149 78 152 84 171 62 153 57 158 49 142 68 164 75 166 56 150 60 160 50 141 80 166 45 136 52 129 55 159 pls help. thanks. Hi, Rightclick on the X-axis --> "Format Axis" -...

how to fix end point of a chart
how to fix end points of a chart for different X & Y value Please clarify your question. What do your end points look like and what do you want to change? "JG" <JG@discussions.microsoft.com> wrote in message news:BCC055D5-4568-43DF-9CDF-98A45D361AFE@microsoft.com... > how to fix end points of a chart for different X & Y value ...

Chart that will expand to aditional rows
I have a chart now with this in the data range: =PHQChartQuery!$A$2:$C$7 But I would like it to look like this =PHQChartQuery!$A$2:$C$500 But when I do there there alot of extra points on the graph. I would like it to only show on the graph only the rows that have data in them and then if I add data later on it will update the graph. So I don't have to keep redoing the range everytime data is entered. Is this possible? Hi, You need to use named ranges with dynamic formula. Have a look at Jon Peltier's page, which contains information on the technique and links to other sit...

Chart select, yet changing set of series
How can I have a chart that *only* plots series chosen by the user. Fo example: I would like to have a on/off (1/0) switch for each series of data. want the chart to only plot series with the on switch value. Also, I' looking for a true solution, not one that plots the off series on th axis. All help is, as always, greatly appreciated -- nam ----------------------------------------------------------------------- name's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3411 View this thread: http://www.excelforum.com/showthread.php?threadid=55945 Oh I...

Word in Notebook Format
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel can i use this notebook format as a diary? If yes, how to add a new page under the same tag? If it work, that would be great!! thanks. I would not trust a "Notebook View" document under continuous editing for very long. They tend to break easily. So "No". Use an ordinary document that you can edit directly over long periods without breaking it. And keep a BACKUP!! Remember there is no such thing as "losing a few pages" of a computer file: you can either read it ALL o...

Default number formatting
Hi, I am currently experiencing some difficulties with my number formatting within Excel 2007. I have a fairly complex series of worksheets, which now seem to be defaulting to the Accounting format. I think that I may have done this through a replace all, somehow setting the cells to all be Accounting format. Peculiarly when I add a new worksheet, the cells also default to Accounting format Is there a simple way in which I can use a combination of Replace all and formatting to reset all the blank cells to be to be in General format? Many thanks, Ben ...

HTML/Texts e-mail format merge issue
On our MS CRM 4.0 rollup 5 we will create regular campaign, assign marketing list to this campaign and create activity with "E-mail via Mail Merge" as an channel. Latter when we will call "Distribute Campaign Activity" with template and choose to send email in HTML format, those emails will go out and will show up in the "E-Mail messages Created View". But if we will choose to send emails in text format, email will go out, but will not show up in the "E-Mail messages Created View". another difference in the both processes, that when we will choose...

Auto Formatting the date field from mmddyyyy to mm/dd/yyyy
Hello, I have the current script in place to format date fields from mmddyyyy to mm/dd/yyyy. I am trying to reduce every possible keystroke and this will eliminate 2. My issue is that CRM fires it's validity check on the date field before the onchange event. Thus, it finds an error in the date because it hasn't had a chance to format. Any suggestions on circumventing the CRM validity check or any other work arounds? Thanks, Brandon // Get the field that fired the event. var oField = event.srcElement; // Validate the field information. if (typeof(oField) != "undefined&...

Using images in bar charts
I know there must be an easy way to do this... I need to use an image of a thermometer, where the red bar in the thermometer replaces the typical bar in the bar chart. I've searched on Google, and found a couple of things, but they're technically beyond my capability! I know you can use a graphic in Excel, but doesn't the graphic end up being distorted? I will continue experimenting and searching for the right answer, but I thought I'd tap into this community and see if I get an easy answer. Try Pope's wonderful site. Search down the list and you will see his t...

Greater than formulas with conditional formatting
Ahhh!!! About to pull my hair out! Hopefully someone out there can help save my hair and my sanity by giving me some excel help! :-) I have roughly 17 cells that all contain sums calculated from other cells. I would like to use conditional formatting (unless there is a better suggestion) to highlight the highest sum. I've tried writting a formula within the conditional formatting that would do this but can't figure out how to write the formula that states if cell A3 is greater than A4, A5, A6, and A7. I've tried it several different ways but I keep getting an error message t...

Convert string into XML format
Hi ! I have a big problem, i'm using XMLdocument to add elements to a XML file. Elements are like this: <name>Paolo</name> <comment>this is <a comment></comment> <description>In italian "is" is = � !</description> the problem is that i want to encode text in name,comment and description. I have tryed to use InnerText property, but it doesn'work for encoding (like was written on a msdn example...................) Any idea ? Alessandro wrote: > I have a big problem, i'm using XMLdocument to add elements to a XML file. >...

exporting graph into a jpeg or bmp format
I need to export my charts/graphs into a jpeg or bmp format so I can post them on a page in the web. The tool that they are posted in only will accept charts and graphs that have bmp or jpg file extensions. I don't see this as an option in Excel...any ideas? Charts should generally not be exported as jpeg. Common other formats are gif and png; bmps are generally very large. You can use VBA to export charts to common graphical formats, depending on your installation. But John Walkenbach's free Chart Tools add-in (http://j-walk.com) can export charts for you, in GIF, PNG, TIF, an...

Labels on a chart
I have a Column Chart where I am charting values in column E. E2 contains the results of =C2/D2, E3 contains the results of = C3/D3, etc. These values are displayed above each of the columns in the chart. What I need to do is also display the value contained in C2 below the column repersenting the result of =c2/D2, I need the value in C3 below the column representing the result of =C3/D3, and so on. How can I get this accomplished? Thank you very much for any insight. Hi Kevin, If you can live with the values of columns C and D being positioned just within the plotarea above the x a...

Determine if data label overlaps chart title?
Hello again, all you experts. I now have run into a new issue when generating charts via VBA. I add data labels only if the datapoint belongs to a particular company. Sometimes, the data label is on the last (and highest) point on a distribution curve. It then runs into the title of the chart. I've been trying to capture this in code but am at a loss. I can't find any property of the data label that tells me its top. I haven't even gotten to trying to compare this with the bottom of the chart title! What I want to do is change the data label's position to bottom i...

formatting dates into weeks
do somebody knows how to translate a date into a week nbr ? I tried something like (date - 01jan) / 7 but it seems hazardous thanks for help joel "joel" <jclaes@brusselsairlines.com> wrote in message news:09cd01c37c2d$4de37270$a401280a@phx.gbl... > do somebody knows how to translate a date into a week nbr ? > I tried something like (date - 01jan) / 7 but it seems > hazardous > thanks for help > joel Calculating a week number from a date is not trivial as weeks do not fit cleanly onto years. There is a WEEKNUM function in the Analysis ToolPak Add-In. Type WEEK...

in publisher how do I set up pages in book format order
Im working on a yearbook and would like to set up my pages in order 2003 version What size paper and pages are you planning to use? -- Don Vancouver, USA "burbanklady" <burbanklady@discussions.microsoft.com> wrote in message news:F27EC13B-FDCF-4DCE-8819-4D8A471CF914@microsoft.com... > Im working on a yearbook and would like to set up my pages in order > 2003 version they are 8.5 x 11 I am ultimately sending all the pages off to a company who is publishing it for me, but I want to easily organize it. "Don Schmidt" wrote: > What size paper and p...

conecting 2 cells with diferent format in 1 cell
i want to have a cell with data from 2 diferent cells, but with diferent format like discription below, and A4 is in diferent sheet: A1 A2 A3 A4 nuno .... 1245 nuno (bold) 1245 A1 A2 A3 A4 filipe .... 1452 filipe (bold) 1452 ............. i can do the conection of A1&A3, but how do i put the number all time below of name, cos i'm not getting there. A4 will do to identifier! Ca...

Line chart Axis labels problem
Hi Trying to produce a chart with wizard from two columns of data: Year 2002 2003 2000 ...

Excel Chart in Word
I have a Word 2000 document with some embedded Excel (2000) charts. The charts themselves are quite small, and so use a very small font (Gill Sans Light @ 5pt). When I print the document, one of the charts looks great, but the others seem to use the wrong font (it looks like Arial). Even if I copy the "good" chart, the copy suffers from the same problem as the others. This is not specific to one machine (a colleague has replicated the problem), nor to one printer (I can get the same result when printing to PDF). My colleague did manage to get all the charts to come out correctly af...

Strange symbol & format in sent e mail messages #2
Recenrlt my e mail messages ( all new ones, and any replies) now contain a symbol that resembles the one used to note a paragraph, or mark each verse in the Bible. The message format also changes, with a dot between each word. How can i revert to "normal" e mail? ...

Conditional Field in Report Writer
Hey, all, I'm looking to create a conditional field in Report Writer. Basically, I'm looking to add a "USD" string to the end of the subtotal/total fields if the customer is American. Here's what I have: Name: docsubtotal Result Type: String Condition: STRIP ( RM_Customer_MSTR.Comment1 ) = "USD" (I use the comment1 field to denote USD customer--long story, have reasons) True Case: CUR_STR ( SOP_HDR_WORK.Subtotal ) # " USD" False Case: CUR_STR ( SOP_HDR_WORK.Subtotal ) Works great, except, any currency formatting is lost. IE, what should be "$...

Date and Time Chart
I have data that consists of a due time, a submitted time, and 3 types of job categories. Essentially, what I would like to have is the date along the x axis, the jobs on the primary axis, a secondary axis with times, a bar chart with the types of jobs and the due times and submitted times to be trend lines in the same graph. Is this possible? If so, what is the best way possible to achieve this? Thank you! You should be able to find something here that helps. http://www.peltiertech.com/Excel/Charts/ComboCharts.html "KC8DCN" <KC8DCN@discussions.microsoft.com> wrote i...

boolean result format TRUE/FALSE or 0/1
Using a boolean expression in Excel 2000 normally (I think) gives a result with the format TRUE or FALSE. However, sometimes I get the result 1 or 0. I cannot change this by normal formatting facilities, nor I can find a sheet option that sets this format. Can anyone tell me what sets the output format of a boolean and how I can control this Thanks in advance Jos It would be interesting to see an example that returns 0 or 1. The only way I know to get this is by mixing Boolean operations with arithmetic ones. So, for example, while =A1>3 will return TRUE or FALSE, =(A1>3)*1 will ...