fixing pie chart colors depending upon value ?

This is really about using an excel chart inside an access report but nobody 
in microsoft.access.reports seems to know the answer.

Using a grouped field in a database I end up with two values per record 
showing the number of 'goods' and the number of 'bads' The pie chart plots 
these two numbers. I want the 'good' slice always to be green and the 'bad' 
slice always to be red.

Setting the format worked OK until I had 7 goods and zero bads, then the 
whole chart was red instead of green.

Any idea how I can fix this?

Howard



0
nospam514 (6)
10/25/2008 5:38:09 PM
excel.charting 18370 articles. 0 followers. Follow

2 Replies
362 Views

Similar Articles

[PageSpeed] 33

The technique described in this blog post will work on any chart type that 
uses a fill color:

http://peltiertech.com/WordPress/2008/03/03/vba-conditional-formatting-of-charts-by-category-label/

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Howard" <nospam@home.please.co.uk> wrote in message 
news:dOWdnZ36w7rkxJ7UnZ2dnUVZ8rCdnZ2d@pipex.net...
> This is really about using an excel chart inside an access report but 
> nobody in microsoft.access.reports seems to know the answer.
>
> Using a grouped field in a database I end up with two values per record 
> showing the number of 'goods' and the number of 'bads' The pie chart plots 
> these two numbers. I want the 'good' slice always to be green and the 
> 'bad' slice always to be red.
>
> Setting the format worked OK until I had 7 goods and zero bads, then the 
> whole chart was red instead of green.
>
> Any idea how I can fix this?
>
> Howard
>
>
> 


0
jonxlmvpNO (4558)
10/26/2008 1:10:49 AM
Thanks, That didn't quite do it but it pointed me in the right direction. In 
case anyone else wants to know how to do it this code finally worked.

Private Sub GroupHeader2_Format(Cancel As Integer, FormatCount As Integer)

Dim v As Variant

v = Me.chart1.SeriesCollection(1).Points.Item(1).DataLabel.Text
If UCase(Left(v, 1)) = "B" Then 'label starts with B - the bad slice, make 
it red
  Me.chart1.SeriesCollection(1).Points.Item(1).Interior.Color = RGB(255, 0, 
0)
Else 'the good slice, make it green
  Me.chart1.SeriesCollection(1).Points.Item(1).Interior.Color = RGB(0, 255, 
0)
End If

End Sub


"Jon Peltier" <jonxlmvpNO@SPAMpeltiertech.com> wrote in message 
news:e2Cf3ewNJHA.1164@TK2MSFTNGP03.phx.gbl...
> The technique described in this blog post will work on any chart type that 
> uses a fill color:
>
> http://peltiertech.com/WordPress/2008/03/03/vba-conditional-formatting-of-charts-by-category-label/
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Tutorials and Custom Solutions
> Peltier Technical Services, Inc. - http://PeltierTech.com
> _______
>
>
> "Howard" <nospam@home.please.co.uk> wrote in message 
> news:dOWdnZ36w7rkxJ7UnZ2dnUVZ8rCdnZ2d@pipex.net...
>> This is really about using an excel chart inside an access report but 
>> nobody in microsoft.access.reports seems to know the answer.
>>
>> Using a grouped field in a database I end up with two values per record 
>> showing the number of 'goods' and the number of 'bads' The pie chart 
>> plots these two numbers. I want the 'good' slice always to be green and 
>> the 'bad' slice always to be red.
>>
>> Setting the format worked OK until I had 7 goods and zero bads, then the 
>> whole chart was red instead of green.
>>
>> Any idea how I can fix this?
>>
>> Howard
>>
>>
>>
>
> 


0
nospam514 (6)
10/26/2008 2:38:57 PM
Reply:

Similar Artilces:

Problem when displaying a value
Hello, I am displaying a value in a textbox of a form (mytextbox.Caption = 14595" , and the result in display is : 1,4595E+04 Is there a way to force Access to display the value as 14595 and not as 1,4595E+04 ? Thank you. I'm confused. If this is a text box, it does not have a Caption property. Just set the Format property to: General Number of possible Fixed. If it is a label, try: MyLabel.Caption = Format(14595, "General Number") -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to g...

Color code quoted text
Is there no way to color code quoted text in the standard message reading view? I've searched high and low and can't seem to find anything about this. The newsreader in Opera does a terrific job of this, as do many other programs. Bob Graham Yes, but since you decided to keep secret the version of Outlook you are = using, all bets are off. --=81 Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. All unsolicited mail sent to my personal account will be deleted without reading. =20 After furious head scratching, rvgrahamsevatenein@sbcglobal...

Summing values where one is #Error doesn't work
I have a raw_data table (originating from an external source and pasted regularly into Access). I have a normalised_query query based on raw_data and doing 'stuff' including converting a text column to a number column. Where this conversion fails (<1% of cases) the column in the query contains #Error. I now want to run another query which includes a sum of that column, but because the value #Error appears in one of the rows, the sum fails. What's the simplest way around this? How are you converting the text to number? You might try something like: IIF(isnumeric([you...

Reverse chart column and row order
I'm running Office 2007 and all my charts are showing up in reverse order: I want a bar graph that looks like this: Series 1 -------------------------------->Column 1 Series 1 ------------------------->Column 2 Series 1 ------------------------------------->Column 3 Series 2 -------------------------------------->Column 1 Series 2------------------>Column 2 Series 2-------------------------->Column 3 What I'm getting is: Series 2-------------------------->Column 3 Series 2------------------>Column 2 Series 2---------------------->Column 1 D3M ----------...

Sum Values depending on other values
I would like to have a formula in a single cell that will sum the values in range E:E if the corresponding value in G:G is >0. for example: Col G Col E 5 1 0 8 9 3 0 7 The sum of Col E is 4. Thanks, Bernie =SUMIF(G:G,">0",E:E) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "bw" <iamnu@cableone.net> wrote in message news:12060mofq6cef5c@corp.supernews.com... > I would like to have a formula in a single cell that will sum the values in > range E:E if the c...

Text box disappears on chart
I added a text box to a chart and when I click off of it, the text box disappears (goes behind the chart). I have tried Bring to Front and Bring Forward, and it may or may not stay visible and print. I have clicked on the frame of the text box (it changes pattern) and tried Bring to Front - same problem. Any ideas? Carole O The text box is on the worksheet, not the chart. To fix the problem, Select the text box, and choose Edit>Cut Select the chart (you'll see handles on its corners and sides) Choose Edit>Paste, to paste the text box onto the chart. Click away from the char...

Chart numbers from multiple sheets
Is Excel 2002 capable of creating a chart based on numbers from multiple sheets in a workbook? Addressed in your other thread. Regards, Leni -- LeninVM ----------------------------------------------------------------------- LeninVMS's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=668 View this thread: http://www.excelforum.com/showthread.php?threadid=31954 ...

Bubble Charts Colours go wrong
Hi, I have some bubble charts which are maintained by a VB script i wrote. The script sets up a data series based on a table with custom labels, sizes and colours for each bubble. I've now come across a problem that despite my macro setting the colour of the bubbles correctly and even the sample colour box in >format data series> patterns showing the colour i want, the bubble does not. please help! Darren. You don't show the code. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services - Tutorials and Custom Solutions - http://PeltierTech.com/ 2006 E...

directing the sum of a cell to either of two cells depending on va
I have a spreadsheet for working out hours worked that ends like this..... Total Hours _ Add Credit brought forward _ Deduct debit BF _ Total Hours for the week _ Less Standard hours - 36 Total _ What i need to do is forward this balance to another copy of this sheet using a formula that says if its a positive number then it goes to the credit cell but if its a negative number then it goes to the debit cell. Hope this makes sense. If not please ask - I need help. Thanks Debi Would 2 seperate "if" functions work for you. In the debit column put, "if&quo...

Duplicate Master Number
OK, so I am having the issue with the duplicate master number. I have 4 records out of 2100 with duplicate master numbers. My thought was to bump the master number forward by 4 and then assign these 4 numbers to the apppropriate document numbers in the SOP10100 and/or SOP30200. Is there anyplace else that I need to be worried about, or some reason that you all know why this wouldn't work? -- Tom Crawford, CPA, MCP You can fix your next master number by running the following query: UPDATE SOP40100 SET NXTMSTNO = ( SELECT MAX(MSTRNUMB) + 1 FROM ( SELECT MAX(MSTRNUMB) AS MSTRN...

How to chart monthly sales for successive years
I want to show monthly figures with a line chart, with the second year coming after the first, the third year after the second, etc. I don't want the years to bunch up, ie 3 years-worth of bars for Jan, Feb, etc. I want Jan-Dec 2006, then Jan-Dec 2007, and so forth. Thanks for your help! Create a table with the dates across the top e.g. Jan-07, Feb-07, etc, then have the monthly sales figures below the dates. Select the dates and the sales numbers and click on insert chart. You can see an example of this at www.tejniya.co.uk, click on documents and goto Excel. MVK "wolf...

Background Color
In my Publisher 2000, I made the background a color for my brochure, however, when I print, the background color does not cover the entire page. Please offer advice. This is a limitation of your printer margins. Might try some colored paper instead. Look at your printer documentation to determine your printer margins or open WordPad, file, page setup, the default margins are usually there but if not input 0 into each margin; it will then default. -- Mary Sauer MS MVP http://office.microsoft.com/clipart/ http://mvps.org/msauer/ "Mark" <info@whisperingcreek.net> wrote in me...

Copying record value from column in multiple sheets into single sh
Hi, I have some employee Records in sheets "Nov07","Dec07" and "Jan08". the first two columns(A and B) are employee number and name in each sheet, and column C is their overtime. I want to create a consolidated sheet "YTD" so that I can see all their YTD overtime in one sheet and do some analysis. Hence the new sheet will have first two columns A and B same as above howver column C, D and E will be the overtime from each sheet. It would have been a simple copy paste if the number of employee had been static, but it changes every months as new em...

Unevenly spaced x axis on chart
Hi Trying to make a chart where the x axis is number-of-days from a start-date, and y axis is a value measured on the particular date. For example, the columns may be: Date Days from Start Measurement 02/12/09 1 120 06/12/09 5 134 09/12/09 8 118 18/12/09 17 109 etc. I want a line chart showing the measurement vs days which I can do - BUT... I would like the x axis (days from start) to be spaced according to the number of days, which as you can see from the above are not evenly spaced. In other words, if there have been several days between measurements, I...

Removing gris effect in a chart
Hello all! I created a contour chart in Excel 2007. But there is a grid effect on the chart. Is it possible to remove it, and how? Thanks a lot! Hi, Select the legend and then select a legend entry. CTRL+1 to display the format dialog. The dialog should show Format Band in its title. Select Border colour and set it to No Line. Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "Olivier" <Olivier@discussions.microsoft.com> wrote in message news:8942BA16-4F43-4983-8057-0FECA1FC1711@microsoft.com... > Hello all! > > I created a contour ch...

created chart will not display
I tried to create a 3D pie chart within a spreadsheet. After highlighting the data that I wanted in the chart , the only thing that shows up is the chart title. The pie chart itself is no where to be found. Please advise Thanks,Belinda That probably means that Excel thinks your data are not numeric. If the values you are plotting are in, say, B2:B5, in some empty column, say C, starting with C2, enter =ISNUMBER(B2) and copy down to C3:C5. What do you get? -- Tushar Mehta http://www.tushar-mehta.com Custom business solutions leveraging a multi-disciplinary approach In Excel 2007 d...

sum a cell value over 75
Please help!! Excel beginner! I need to build a formula that will add any remaining value over 75 in a cell. For example: In cell A1 = 76.5 In cell A2 - I need a formula that will sum anything over 75 and enter 1.5 into cell A2. Thanks in advance for everyone's help!! > For example: > In cell A1 = 76.5 > In cell A2 - I need a formula that will sum anything over 75 and enter 1.5 > into cell A2. Perhaps, as a start .. try in A2: =IF(A1>75,1.5,"") A2 will return 1.5 if A1 contains a number exceeding 75, otherwise A2 will just appear empty ("") -- ...

Activating a sound alert on cell value
I have a excel book where I link on-line quotes from internet. HOW DO I SET UP AN ALERT TO ACTIVATE A SOUND WHEN THE CONTENT IN A CERTAIN CELL REACHES A PRE-DETERMINED VALUE? I have not worked with Visual basic before and its Excel 2003 Im using. Can anyone help please? (It would be great if the formula is in Swedish). Kind Regards, Sebastian Flyte ...

Sort Column by Text Color or BackGrd Color
Does anyone have a method that allows you to sort by Text Color or by Background Color? Darn! It's so cold here in Massachussetts that my fingers hit the wrong key. Check out Chip Pearson's page. http://www.cpearson.com/excel/SortByColor.htm Good Luck, Mark Graesser mark_graesser@yahoo.com ----- Ken Dickens wrote: ----- Does anyone have a method that allows you to sort by Text Color or by Background Color? Ken, The principle is to create a UDF to calculate the Colorindex and add it to an adjacent column, and then sort by that column. Chip Pea...

Charts for Social Work
Need help in creating 2 charts. Not sure if it�s possible in Excel. Hopefully I�ll give enough details. Chart #1 Y-axis: Range is 35 to 5, where the 35 is a minimum value at the botto of the chart. X-axis: There are actually three categories, but it is not a Bar Chart per se. Category Names(should appear at the top of the chart, not as an axi label): Baseline, Intervention, and Follow-up (The X-axis should hav gaps, so each category is separated; there actually should be no axi label) The Y-axis represents a score between 5 and 35. The score for th Baseline is 22 and is supposed to be re...

Excel should allow chart axes to be set from sheet data.
I often chart account balances where I have x axis of dates and y axis of monetary amounts. It would be helpful if there was a facility to allow the x axis minimum and maximum to be set to some value as calculated from the data itself (or even calculated using a formula). I like to keep a "rolling" picture of the last three months (so, today()-90 to today()) and the next three months (today() to today() + 90) (on two separate charts). I have to manually update the x axis's minimum and maximum each day. you can set up formulas in cells to calculated your desired max/mins a...

Cannot change criteria on copied chart without changing original c
I have a chart that I created from a pivot table. Instead of creating 40 plus charts individually, I tried to copy the original chart and change the location for each chart. When I change the facility location for one chart it changes all others. How can I get around this? Steve You'll need to make independent pivot tables for each chart. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services 774-275-0064 208-485-0691 fax jon@peltiertech.com http://PeltierTech.com/ _______ "Steve" <Steve@discussions.microsoft.com> wrote in message news:07FF39D...

scroll bar missing in charts
I don't know how I managed to do it, but I have locked my charts into being viewed as "fit to window." I have no scroll bars. I checked to make sure the Tools/Options/View horizonal and vertical scrollbars were checked, and Tools/Options/Chart, and unchecked "chart sizes with window frame." Nothing happened, and when I reopened the Tools/Options/Chart, the box was re- checked! I can't uncheck "fit to window" in the view mode, and I can't click on zoom. Please HELP!!! You haven't by any chance just hit View / FullScreen have you, in whi...

Create an auto chart using macro
Hi all, I would like automatically generate a Gantt style graph though use of a Macro and a little button. I have the following data Task description Start date End date % completed % remaining. Does anyone have some code that I can just use? Haha, I think it's going to be a little more complex then you think it's going to be. Start here: http://www.vertex42.com/ExcelTemplates/excel-gantt-chart.html You should probably get acquainted with that. There's a video there too. May be a good learning experience, but I think you already know a bit about G...

Icon Colors
I just installed Office 2003 and I want to change the color of the icon that > I am pointing to in Excel. In addition the color of the cell locator A, 1 for example > is the same obnoxious orange color. Where do go to to change these colors > without changing the XP color scheme (XP style, silver)? -- Profkay Dr. Kay You do not change these colors without changing the Windows color scheme or theme. Gord Dibben MS Excel MVP On Sat, 7 Apr 2007 04:10:00 -0700, Dr. Kay <DrKay@discussions.microsoft.com> wrote: > I just installed Office 2003 and I want to change the ...