Changing format of a particular point in a series (conditionally/vba)

I have a single series which is charted each month and want to
automatically format one of the points (a bar) a different colour.

Is it possible to do this automatically?

thanks for any advice

david marcus - note the email address will not work
0
dmarcus (1)
9/10/2004 7:12:05 AM
excel.charting 18370 articles. 0 followers. Follow

4 Replies
373 Views

Similar Articles

[PageSpeed] 21

Hi!
Click once on the particular bar where you wish to have 
different color, system will select all bars in the chart.
Click once again, the system will select only the bar 
that you are clicking on.  Call out Format Data Point and 
select the desired color or pattern.


>-----Original Message-----
>I have a single series which is charted each month and 
want to
>automatically format one of the points (a bar) a 
different colour.
>
>Is it possible to do this automatically?
>
>thanks for any advice
>
>david marcus - note the email address will not work
>.
>
0
anonymous (74722)
9/10/2004 7:21:11 AM
One more step. Turn on the macro recorder while you do the formatting, 
then insert the appropriate lines into a clause like this:

   With ActiveChart.SeriesCollection(i).Points(j)

     ' format the point here

   End With

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

Poh wrote:

> Hi!
> Click once on the particular bar where you wish to have 
> different color, system will select all bars in the chart.
> Click once again, the system will select only the bar 
> that you are clicking on.  Call out Format Data Point and 
> select the desired color or pattern.
> 
> 
> 
>>-----Original Message-----
>>I have a single series which is charted each month and 
> 
> want to
> 
>>automatically format one of the points (a bar) a 
> 
> different colour.
> 
>>Is it possible to do this automatically?
>>
>>thanks for any advice
>>
>>david marcus - note the email address will not work
>>.
>>

0
9/10/2004 1:11:17 PM
Thanks, but the problem (which I should have stated more clearly) is that 
the order of the point may vary - is it possible to refer to it by its name, 
rather than the points(index)??

David marcus

<jonxlmvp@peltiertech.com
> One more step. Turn on the macro recorder while you do the formatting, 
> then insert the appropriate lines into a clause like this:
>
>   With ActiveChart.SeriesCollection(i).Points(j)
>
>     ' format the point here
>
>   End With
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Peltier Technical Services
> Tutorials and Custom Solutions
> http://PeltierTech.com/
> _______
>
> Poh wrote:
>
>> Hi!
>> Click once on the particular bar where you wish to have different color, 
>> system will select all bars in the chart.
>> Click once again, the system will select only the bar that you are 
>> clicking on.  Call out Format Data Point and select the desired color or 
>> pattern.
>>
>>
>>
>>>-----Original Message-----
>>>I have a single series which is charted each month and
>>
>> want to
>>
>>>automatically format one of the points (a bar) a
>>
>> different colour.
>>
>>>Is it possible to do this automatically?
>>>
>>>thanks for any advice
>>>
>>>david marcus - note the email address will not work
>>>.
>>>
> 


0
dmarcus1 (1)
9/10/2004 11:57:51 PM
David -

By "Name" of the point, you mean the category label? The first macro in 
this post formats points based on the X label for that point.

   http://www.google.com/groups?selm=OItoyvW9BHA.2028%40tkmsftngp04

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

david marcus wrote:
> Thanks, but the problem (which I should have stated more clearly) is that 
> the order of the point may vary - is it possible to refer to it by its name, 
> rather than the points(index)??
> 
> David marcus
> 
> <jonxlmvp@peltiertech.com
> 
>>One more step. Turn on the macro recorder while you do the formatting, 
>>then insert the appropriate lines into a clause like this:
>>
>>  With ActiveChart.SeriesCollection(i).Points(j)
>>
>>    ' format the point here
>>
>>  End With
>>
>>- Jon
>>-------
>>Jon Peltier, Microsoft Excel MVP
>>Peltier Technical Services
>>Tutorials and Custom Solutions
>>http://PeltierTech.com/
>>_______
>>
>>Poh wrote:
>>
>>
>>>Hi!
>>>Click once on the particular bar where you wish to have different color, 
>>>system will select all bars in the chart.
>>>Click once again, the system will select only the bar that you are 
>>>clicking on.  Call out Format Data Point and select the desired color or 
>>>pattern.
>>>
>>>
>>>
>>>
>>>>-----Original Message-----
>>>>I have a single series which is charted each month and
>>>
>>>want to
>>>
>>>
>>>>automatically format one of the points (a bar) a
>>>
>>>different colour.
>>>
>>>
>>>>Is it possible to do this automatically?
>>>>
>>>>thanks for any advice
>>>>
>>>>david marcus - note the email address will not work
>>>>.
>>>>
>>
> 
> 

0
9/11/2004 12:57:40 AM
Reply:

Similar Artilces:

Excel 2007 Need to permanently change Normal settings for gridlines
I am using Excel 2007 for the first time and find the gridlines delineating the cells are so faint as to be nearly indistinguishable. I can go into the cell formatting and modify the normal style, and it is just the way I want it. But I can't find a way to make Excel remember this and treat it as the new definition of the Normal style. I don't want to have to redefine Normal every time I open a new spreadsheet. Someone please help! Regards Leonard Priestley The changes you describe are changing the Border color and NOT gridlines. Go into Excel Options and cl...

Conditional charts
Is there a way to change the color of a chart bar based on the value of the cell that generates that particular bar? In other words, I have a chart with 15 vertical bars and I want to change the color of one or more of the bars if its value goes above 100% (but not all of the bars). JWeinberg Jon Peltier has instructions for conditional charts: http://www.peltiertech.com/Excel/Charts/format.html#CondChart Jeff wrote: > Is there a way to change the color of a chart bar based on the value of the > cell that generates that particular bar? In other words, I have a chart > wi...

Changing which database Business Portal points to
If I install Business Portal 2.5 (GP) and point it to our Great Plains development server with an old Dynamics database, once we decide to take it live, how do I go back and point it to the live Great Plains server? Is this something that can only be done during the installation? Let me preface my response by saying I only have experience with BP 3.0.... In BP 3.0 several tables are created in the Dynamics database to support the various BP functions. Unless you plan to move or copy those tables you will probably have to reinstall BP 2.5 in order to have them created. Our attempt to cop...

transfer inbox in table format to word
In my older version I could cut and paste the table format in Outlook to a word file. Now I don't seem to be able to do it although I can print the file in that format within Outlook. When I export the inbox to word I get the whole text not just the headings. Is it possible to transfer the table format to a word file? ...

Changing Titles by a formula
From cell A1:A10 I have standard titles that are linked to another sheet. I only need to change the office and date per each title. Is thier a way to do that making the office and date a formula in another cell? example of title ABC Comp, Office 38L Sales as of 6/07/2004 I need to only keep changeing the 38L and date. Any suggestions----Thanks Say Office # is in Column 'C' in cell A1: ="ABC Comp, Office "&C1&" Sales as of "&Today() ----- Heather wrote: ----- From cell A1:A10 I have standard titles that are linked to ...

problem in changing the text of sentences before tables
I am developing a word automation application. In a method of mine, I change the text of some sentences of an opened word file, but the problem is when I change the text of a sentence which located before a table, it will be moved to the first cell of the table. My code is as follow: void myMethod( long startingSentenceNumber, const char *toBeSearched, const char *replacement, bool replace ) { Range currentSentenceRange; Selection sentenceSelection; Sentences sentencesList = m_document.GetSentences(); long sentencesCount = sentencesList.GetCount(); CString replacementCStr(...

Question Regarding Excel 2007 Formatting Corruption
Hi Folks, I am having a problem with Excel 2007 files losing all formatting (merged cells, colors, borders, and data formatting (99% turns into 0.99)) when I open a file on our office server make edits and then save the new file on the server. Each sheet usually has a mix of locked and unlocked cells and I unprotect the sheet to make edits. Also, something is fundamentally changed with the file as its size doubles or triples. If I reopen the corrupted file and redo any of the formatting and try to save it none of the new formatting is retained either. Has anyone else ever experienced a pr...

Count occurrances with conditions
I am trying to count in 3 columns. Column A will have entries of "B or "S" and columns B and C will have "1" or "0". A B C B 0 1 B 1 0 B 1 1 S 1 1 I want to count how many times column C has a 1 with a B or S. That i no problem, I can do that. I also want to count the number of time column B has a 1 with a B or S. That is no problem, I can do that. However, the third count I want to count is when a 1 appears in bot column B and...

can't change/delete bills and deposits
suddenly one day i realized that when i tried to change a bill, i could click "ok" but nothing would happen. I thought it was because i upgraded to 2004, so i deleted the bills and rescheduled. about 5 of the bills will not delete either. the error message is: "Money could not write to your Money file. The operation could not be performed or another application finished this task before you. Please try again." Now some of the newly scheduled bills will not allow me to change the properties, i click ok but nothing happens when i edit the series. I have repaired ...

Outlook 2007: When i Save a New Rule outlook Change it automatically...
Hi people! I have this problem with Outlook 2007. I have 15 e-mails addresses in my outlook, but i can=B4t create the rules for send all the received mails to each emails folder. Example: I want to create a simple rule that move all the e-mail that i get from "example1@example1.com" to the folder "Inbox/ example1@example1.com". When i create the rule, all works fine, i select the correct e-mail account name, the correct destination folder, etc... but when I hit the SAVE button in rules window, Outlook 2007 changes the account automatically to another one. Not automatically...

Change Row/Column Height & Width
I know I should be able to automatically set a row height to the max necessary by hovering the cursor between the 2 rows I want to adjust and double-clicking, but sometimes this doesn't always work. Why is that that - do I need to adjust a setting? And is there any way to set it so that if text is added or deleted the row height would change automatically so thatthe text fit appropriately? Set the row format to Autofit and cells to Wrap Text Gord Dibben MS Excel MVP On Tue, 19 May 2009 12:14:04 -0700, DaveL <DaveL@discussions.microsoft.com> wrote: >I know I should be ...

Changing Functional Currency #4
Try to change the functional currency to Z-CNY. ERROR = Activity for this currency already exist. Finance made 2 GL transactions. Can we remove the transactions (Tools >> Utilities >> Financial >> Remove History) or (File >> Maintenance >> Clear Data ) then change the functional currency? Which tables should I remove history or clear data. Can someone on the board help us? regards, Once you process a transaction, you are stuck with the functional currency. I have not seen a table fix for this problem on the board. Consider creating a new database an...

Change default "SaveAs" to *.msg rather than *.html
Hi!, Using Outlook 03, I regularly need to save emails and prefer to save as *.msg so the attachments are contained within the one file. How can you change the default SaveAs command to Save as Type in a *.msg format (rather than *.html) so the type is already *.msg without having to use the pull down? Thank you Troy Outlook provides no way to change that. -- Sue Mosher, Outlook MVP Author of Microsoft Outlook Programming - Jumpstart for Administrators, Power Users, and Developers http://www.outlookcode.com/jumpstart.aspx "TroyB" <boeky72@hotmail.com>...

change column name from letters to numbers?
I'm importing a 3rd party CSV file into Excel and then comparing some of the contents to a printed spec. Each numbered field in the spec corresponds to a column in the work sheet. I'd like to change the column headers from letters to numbers. Is that possible? Tools-->Options, General tab. Check the R1C1 Reference Style. ******************* ~Anne Troy www.OfficeArticles.com www.MyExpertsOnline.com "Jerry" <jerry@nowhere.com> wrote in message news:OoyPef$eFHA.3048@TK2MSFTNGP12.phx.gbl... > I'm importing a 3rd party CSV file into Excel and then comparing ...

Export Format not avaiable
"The Format in which you are attempting to output the currentobject is not available." I hate access sometimes. It just get's weird, throwing bogus error messages all over the place. I have about 30 seperate queries that I run out to spreadsheets via macro. I have already found out that things can get all screwed up, (meaning it bombs) when those spreadsheets already exist, so the first thing I do is delete the existing spreadsheets, then let them rip. I run into this every once in a while: 20 or so queries into my macro, a query will fail with the above er...

vba to prevent display of default series in doughnut chart
I am generating a doughnut chart using VBA and am having two immediat issues: 1) I am using the following line to ensure that the default chart tha is generated when I invoke the Charts.Add method is a doughnut chart: Application.SetDefaultChart FormatName:=xlDoughnut Is there a more elegant approach to ensuring that any chart create ends up being a doughnut chart, maybe when invoking the Charts.Ad method? I have tried to specify ChartType = xlDoughnut after th Charts.Add method but this does not do the trick every time (see cod snippet below). 2) When the doughnut chart is added (immediat...

Changing of range (Address) to (Cell)
Hi people I have encounter a problem with the use of range From a recorded macro, it's listed this way ActiveChart.SetSourceData Source:=Sheets("Trend").Range("A1:M2,A24:M28"), PlotBy:=xlRow And I edit it to this way ActiveChart.SetSourceData Source:=Sheets("Trend").Range("A1:M2," & Cells(StartX, StartY), Cells(LastX, LastY)), PlotBy:=xlRow And obviously VBA compiler won't let me go this easily, it happen to give an "evil-comment" on my source range, May i know how can i solve this Thank You Hi Kaiyang, Try this, assuming the...

xy scatter format
Hi Hope someone can help. I have a chart i want to make look a little more professional. The chart plots three points within two boxes. the points should be inside the box box 1 has the following coordinates x y 15 70000 15 115000 21.5 155800 30 155800 30 115000 25 70000 15 70000 the chart scale is x 14-32 & y 60000-160000 I would like to make the plot area outside the box black to indicate the no go area. Is this possible? Then to take it further there should be a further smaller box (coordinates unimportant) inside the 1st box to indicate a warning. I would like the plot area ...

Net change and percentage of change
Hi, I have a list of parts that have had price increases. One column has the old price the other has the new price. I need to show the difference, whether it is positive or negative and the percentage of the increase or decrease. The difference is not a problem. The problem lies in calculating the percentage. I don't how to do that. Can someone tell me? Thanks, Richard =(B1-A1)/B1 "Richard" wrote: > Hi, > > I have a list of parts that have had price increases. One column has the old > price the other has the new price. I need to show the difference, whethe...

condional formating count question
Hi, I have a row in which I have a condition that if two numbers are identical its formating the 2nd number in Red Font, Is there a way or formula that I can use to count these red font Numbers? Thanks in advance for you help, It is best to use the same means in a formula that you used to make the font RED in the first place rather than trying to use a function to check the color of a cell. You can take a look at Chip Pearson's page, Functions For Working With Cell Colors http://www.cpearson.com/excel/colors.htm for counting cells in a range with involving normal cell coloring for ...

Changing Average Cost GP 9.0 Error
When we try to change the average cost information for an item per the documentation, (tools >> utilities >> inventory >> change average cost), we get this message. "Use the Inventory Adjust Costs window to change the cost of one or more reciepts. The average cost of an item is recalculated when teh changes are proceses" We would like to change it without using the Inventory Adjust Costs window because there are no recent purchases and receipts of the particular item in question Would anyone have any suggestions? The average cost is the aver...

Copy & paste in multiple areas using VBA
I'm trying to select a series of ranges to 1. paste a named formula I've created and then 2. remove the formula & leave the value What I have so far is shown below, but I'm sure there must be a better way. The problem seems to be that I can't use the copy function in a multiple range. I guess I can't paste xlValues to multiple ranges either, so I'm being forced to handle each range, one at a time, which seems rather cumbersome. Sub Macro1() Range("G8:R45,G50:R59,G63:R110,G114:R121,G114:R122,G126:R134").Select Selection = "=ITNBu...

Microsoft Office Icons Change...
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel I changed the basic icons that came with the office programs, but once I use one of the programs such as Word, the icon changes back to the default one. Even after I quit the program it stays as the default icon. Only when I manually change it does it use the new (better looking) icons I have, but only if I haven't used the program before.... Any ideas? On 6/25/09 2:07 AM, in article 59b7718d.-1@webcrossing.caR9absDaxw, "grantbiz@officeformac.com" <grantbiz@officeformac.com> wrote: > Version: 2...

How do I change the default paper size (and units) in Publisher?
I've got the printer set to A4, but no matter how many times i change the options in Publisher - everytime Publisher loads it resets to Letter/Inches. vIQleS wrote: > I've got the printer set to A4, but no matter how many > times i change the options in Publisher - everytime > Publisher loads it resets to Letter/Inches. ================================= Go to Control Panel and carefully check the Preferences and or Default settings for your printer. There may be more than one place to change paper size. -- ******John Inzer******** **MS Picture It! MVP** *****Digital I...

Creating a VBA Macro loop "doing" stuff on each file in a folder
Hey guys I have this folder containing several workbooks, and the number of files will variate/increase Foldername: ReportA containing files: aaaa.xls dddd.xls dfger.xls otgrr.xls etc In this folder I want to copy all data from the sheet "Sheet1" in each workbook and paste it into a "sheet1" in my summaryfile somwhere else outside this folder. At the present I have many macros in my summaryfile - one pr each file in "ReportA" - doing the same shit on every singel file. The effect is that I have to make one more macro for every new file in the folder. I think th...