Macro commands to assign colors to each line in an Excel chart.

Is there a macro command that will assign a user specifide color to each 
individual line of a scatter graph?
0
JohnnyC (2)
7/2/2009 6:58:01 PM
excel.charting 18370 articles. 0 followers. Follow

3 Replies
1209 Views

Similar Articles

[PageSpeed] 32

Hi,

First, scatter charts do not usually display as lines, so when you say line 
what do you mean?  You chosen as style with connecting lines? or you are 
refering to drop lines, or error bar lines, or...?

You can tell Excel to vary the color by point but if you want to assign 
specific colors to specific points you will need to write a macro.  What 
version of Excel are you using?


-- 
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"JohnnyC" wrote:

> Is there a macro command that will assign a user specifide color to each 
> individual line of a scatter graph?
0
7/2/2009 10:13:01 PM
Hi,

Here is a macro approach:  suppose your data is in the range A2:B12, then 
format each of the cell in C2:C12 to the desired color you want to apply to 
the line.  Then select the chart and run the following macro:

Sub ColorLines()
    Dim cell As Range
    Dim I As Integer
    I = 1
    For Each cell In Sheets("Sheet3").Range("C2:C12")
        ActiveChart.SeriesCollection(1).Points(I).Select
        Selection.Border.ColorIndex = cell.Interior.ColorIndex
        I = I + 1
    Next cell
End Sub

Remember the first color is ignored because there is no line associated with 
the first data point.

-- 
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"JohnnyC" wrote:

> Is there a macro command that will assign a user specifide color to each 
> individual line of a scatter graph?
0
7/2/2009 10:28:01 PM
I have just started using Excel 2007 and have a macro that is not working 
from the 2003 version.  Here is the old code for adjusting the line color for 
the third line in the XYScatterLines graph :

    ActiveChart.Legend.LegendEntries(3).LegendKey.Select
    With Selection.Border
        .ColorIndex = 3
        .Weight = xlThin
        .LineStyle = xlContinuous
    End With
    With Selection
        .MarkerBackgroundColorIndex = 3
        .MarkerForegroundColorIndex = 3
        .MarkerStyle = xlTriangle
        .Smooth = False
        .MarkerSize = 5
        .Shadow = False
    End With

The old version would make the triangles and line connecting the triangles 
red, but the new version makes the triangles red, the line connecting the 
triangles is not.  Any help would be greatly appreciated.

JohnnyC
"Shane Devenshire" wrote:

> Hi,
> 
> Here is a macro approach:  suppose your data is in the range A2:B12, then 
> format each of the cell in C2:C12 to the desired color you want to apply to 
> the line.  Then select the chart and run the following macro:
> 
> Sub ColorLines()
>     Dim cell As Range
>     Dim I As Integer
>     I = 1
>     For Each cell In Sheets("Sheet3").Range("C2:C12")
>         ActiveChart.SeriesCollection(1).Points(I).Select
>         Selection.Border.ColorIndex = cell.Interior.ColorIndex
>         I = I + 1
>     Next cell
> End Sub
> 
> Remember the first color is ignored because there is no line associated with 
> the first data point.
> 
> -- 
> If this helps, please click the Yes button.
> 
> Cheers,
> Shane Devenshire
> 
> 
> "JohnnyC" wrote:
> 
> > Is there a macro command that will assign a user specifide color to each 
> > individual line of a scatter graph?
0
JohnnyC (2)
7/6/2009 10:46:01 AM
Reply:

Similar Artilces:

Creating and Organization Chart from and excel file
I was able to create an org. chart awhile ago and now when I try to use the org. chart wizard and select the excel file I get an error "cannot read names". I have excel and visio 2003. Can anyone help???? I want to include 4 fields from from excel file (name, title, department, reports_to). Help! Have you tried creating an Excel file recently? Perhaps there's an issue with your existing Excel file. I'd try generating a dummy file using the wizard, and then try importing it iback nto a new Visio file using the wizard. If that works, then you may want to cut / pas...

Opening a new instance of Excel
I am using multiple monitors for work and it is great! Is there a setting that I can use so that it opens each new excel file in a new excel window so I can drag different ones to each monitor? Is there a similar setting for Word? I am using Excel 2002 and Word 2002. Thank you. Hi, Yes, you can check the Windows in Taskbar checkbox in Tools; Options. This is on the View tab for both Word and Excel. >-----Original Message----- >I am using multiple monitors for work and it is great! Is >there a setting that I can use so that it opens each new >excel file in a new excel ...

Remove page break in Excel?
How do I remove a manual page break in Excel with Office 2003? Select a cell below the page break then insert, remove page break -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "Page Breaks in Excel Removal" <Page Breaks in Excel Removal@discussions.microsoft.com> wrote in message news:E56F3C94-66AF-4500-A776-32227DFCB1BD@microsoft.com... > How do I remove a manual page break in Excel with Office 2003? ...

VBA from another app: Suppressing Excel confirmation dialog?
After creating/formatting several worksheets from MS Access, I'd like to delete the "Sheetn" worksheets that got put there when I did a .WorkBooks.Add. I avoided using them because I'm not sure how/why they are created - i.e. maybe some user's defaults would only create 1 empty sheet or none. So, form MS Access's VBA I'd like to do: On Error Resume Next .Worksheets("Sheet1").Delete .Worksheets("Sheet2").Delete .Worksheets("Sheet3").Delete .Worksheets("Sheet4").Delete On Erro...

Importing Data into an Excel Pivot Table via Access
I have set up a query in Microsoft Access which is linked to our AS400 server. I have created pararmeters within Access which asks for certain fields which works. I then go into Excel and create a pivot table with the external data source that I have created in access. When I go to enter a pararmeter within Microsof Query I get a reply saying that "Parameters can not be used with this Query", what I want to do is setup a parameter on the Excel spreadsheet which then goes and gets the data i require from this parameter. I would be very grateful if someone could help me with thi...

how do I add times in Excel and result in hours & mins
I want to insert a time when I start work and a time when I take a break, then a time when I leave work. Following that I want to be able to add up the amount of hours that I have worked. This will enable me to plan my week ahead and ensure I only allocate a specific amount of time to a project. http://www.cpearson.com/excel/datetime.htm#WorkHours -- Kind Regards, Niek Otten Microsoft MVP - Excel "Rty Shaw" <Rty Shaw@discussions.microsoft.com> wrote in message news:37D03D72-5525-4D6E-8ED7-2911B16248B0@microsoft.com... >I want to insert a time when I start work and...

Illegal operation error while printing EXCEL or WORD Files
Hi, I am facing an illegal operation error when i try to print any file from excel (any no. of pages), this happens in stand alone printer as well as a networked printer. When we press the print button, it flashes this message, but still prints, but once the printing is completed, i will have to restart the PC. Due to this error other applications PRINTING also will NOT HAPPEN and the only way out is, restart the PC. This happens not only in EXCEL, it happens in all the MS applications (outlook, access, front page, powerpoint also). When I check the print manager (before restart),...

Line measurement
When I draw a line is there a way to put the length measurement on top of the line? I am drawing the footprint of a house for instance. I have all the measurements. How do I go about drawing each measurement as one line while it telling me the length between connection points? isometrics has such dimensioning...actually updates as you change the shape dimension "Matt" wrote: > When I draw a line is there a way to put the length measurement on top of the > line? I am drawing the footprint of a house for instance. I have all the > measurements. How do I go about...

Macro must return the name of the button
Hi there, I want to a macro to display the name of the button (or object) from where I am calling that macro. Anyone an idea? thanks, Derek What sort of button (userform, worksheet forms, worksheet control toolbox)? -- HTH Bob Phillips (remove xxx from email address if mailing direct) "Derek Brussels" <Derek Brussels@discussions.microsoft.com> wrote in message news:8441F83B-89DC-4515-A643-CE8F258DFC1F@microsoft.com... > Hi there, > > I want to a macro to display the name of the button (or object) from where I > am calling that macro. Anyone an idea? > &...

Emailing in excel 2003 02-26-10
If i type in the cell A34: neil.Holden@test.com and press a button is it possible to email to the address of what ever is in A34 is? The email body should say: this has been submitted for cell B34 and todays date. Thanks. Check out Ron De Bruins "Send-Mail" tips: http://www.rondebruin.nl/sendmail.htm Micky "Neil Holden" wrote: > If i type in the cell A34: neil.Holden@test.com and press a button is it > possible to email to the address of what ever is in A34 is? > > The email body should say: this has been submitted for cell B34 and...

Does anyone have a dashboard gauge (speedometer style) for Excel?
I am trying to create dashboard charts from Excel data and would love other templates not available in Excel today - speedometer charts, multi-dimension comparitive charts, charts that build information overlays. I regularly create these in a manual way for executive and customer summaries but would appreciate the ability to automatically generate these types of charts allowing for real time viewing of "what if" scenarios. Steve, there are tons of these things out there to review, few better than this collection: http://www.andypope.info/charts.htm Andy Pope has put together...

Excel Crash
I use Excel and Word 2003 using Windows NT. I've kept some files on a jump drive so I can work on them at home. I attempted to work on a Word documents which had an Excel worksheet inserted in it. I tried double clicking on the worksheet to edit it and Word and Excel shut down. Now when I attempt to open Excel at home it asks for my Office XP Professional installation cd. (I have Office XP at home with Windows XP). I'm having a hard time locating my original discs. Does anyone have any suggestions or experience anything like this? ...

Chart Stopped Updating
Hi I have a number of DDE links that I use to increment Rows D to I every second, providing me with a method of data logging my DDE Links. I find that my chart works fine up to about 247 rows then stops updating. I have just upgraded from 2003 to 2007 version of Excel. Any ideas. Thanks Alec when you saved into 2007, you keep the old file format or use the new format? if using new, pls make sure you are using xlsm. if you are using the compatibility mode, please check make to make sure you click the security setting to low. "Alectrical" wrote: > Hi ...

Pie Chart Question
Hi, I am tring to make a pie chart that needs to include 2 pieces of information for one series. I have two columns of information (number and value) that relate to one person. There are 5 people all together. How do I do this? Technically possible, you might create a bar-pie chart or a pie-of-pie chart. IMO, this would be a TERRIBLE idea. Pie charts should be forgotten, IMO, because they often distort data and make it very hard to actually read the data. http://peltiertech.com/WordPress/whats-worse-than-a-pie-chart/ My recommendation would be to make a clustered chart, a ...

EXCEL TROUBLESHOOTING #2
I have an excel file (2000 format), that after I made a number of changes is causing me problems when I re-open the file. Windows task manager goes to 100% CPU activity, and i cant do anything within the excel file. However, if I set recalculation to manual before I open the file, all seems fine. Obvioulsy I have a problem. But how do i find that problem ? Thanks in advance. I have had some experience running large spreadsheets lately. Above a certain size, the recalculation time seems to climb very fast. While Excel is recalculating, you can't do anything anyway. Best in my v...

Excel Edit F2 button changed for Mac???
Switched to Microsofts version of Excel for Mac. Can anyone tell me what keystroke allows me to edit a cell? Before I switched to a Mac it was the F2 button. Please help. Thank you. See the answers in the m.p.mac.office.excel newsgroup. In article <1176582208.958694.269620@q75g2000hsh.googlegroups.com>, ssears@indy.tds.net wrote: > Switched to Microsofts version of Excel for Mac. Can anyone tell me > what keystroke allows me to edit a cell? Before I switched to a Mac > it was the F2 button. Please help. Thank you. ...

Excel Cells Contents to HTML Pages
Hi, I have an excel sheet which has two columns. Column 1: PageName Column 2: HTMLCode PageName has information like: OurServices.htm which is the name of th HTML page. HTMLCode clumn has actual HTML Code like: <html> <head> <meta http-equiv="Content-Language" content="en-us"> <meta name="GENERATOR" content="Microsoft FrontPage 5.0"> <meta name="ProgId" content="FrontPage.Editor.Document"> <meta http-equiv="Content-Type" content="text/html charset=windows-1252"> ...

In Excel program shows a broken line and don't know how to get ri.
I am trying to put in entries in Excel program and when I go to Print Preview, it only shows part of the entries I put in. When I go to the last entry that it shows in Print Preview, there is a broken line between it and the next entry. I don't know what I did. I could use some help You probably have multiple areas selected for your print area. you can check this by <view><page break preview> One possibility to correct this would be <file><print area><clear print area> or specifically select the area you want printed and <file ><Print area>...

Urgent
How do I get my X axis and Y axis to auto scale inside my chart when I add new data in my spreadsheet? -- Sandi Gardner "Sandi" <Sandi@discussions.microsoft.com> wrote in message news:E4D8613F-13C3-4C1B-9576-95E44B0AE156@microsoft.com... > How do I get my X axis and Y axis to auto scale inside my chart when I add > new data in my spreadsheet? The default is to do so. Check Format Axis/ Scale, and ensure that Auto is ticked for Min & Max. -- David Biddulph That works for my Y axis but I also need my X axis to auto scale also. -- Sandi Gardner "David B...

Chart
From: <c@c.c> Subject: Chart - Title Widths Date: Tuesday, February 03, 2004 12:43 PM Chart - Title Widths Does anyone know how to find the width of a title/axis title? ...

macros disabled
I've just upgraded to Office 2007. Now, when I tried to run a program, it says, "The macros in this project are disabled." What to do? Thanks, Scott I've got it. It was due to the "Missing" references. Mr. Chip Pearson helped identify the problem. "Scott" wrote: > I've just upgraded to Office 2007. Now, when I tried to run a program, it > says, "The macros in this project are disabled." What to do? > > Thanks, > Scott Just as with 2003 you need to enable macros. But, unless the VBIDE is open, you don&#...

How to draw a selection line?
I want to draw selection lines with 1 pixel width dotted line style. normally it works ok, but the it looks not very good when I zoom the image to a bigger magnification. What I want is like the effect in many popular image processing softwares such as Adobe Photoshop. for example, I make a ellipic selection and draw a dotted line ellipse it looks ok. but when zooming in it still the same as normal ,but in photoshop it became aliasing lines which seems to be composed by several small blocks. Does any have any idea on this question?? any help would be appreciated. ...

Does anyone know where I can download EXCEL.MSI?
I am missing my Excel 2002 CD, and can't install Office Update Service Pack 3 without this file. ...

Excel ignores boot-time regional settings when interpreting a date
I have a third party DDE app that exports dates as strings, correctly using the short date format in the regional and language settings, which, in my case is MM/DD/YYYY (reset at every boot). Unfortunately excel insists in interpreting that date as DD/MM/YYYY [Application.International(xlDateOrder)=1, it should be 0], disregarding my regional settings. The result is that all the dates are wrong or #VALUES! If this is not wierd enough, listen to this: it only happens after I reboot (when the date format is reset to MM/DD/YYYY). If subsequently I change the short date format in the regional se...

Excel to check for regular expression?
Hi, How do you use Regular expression in excel? For eg: Check a column of data to see whether are all of them valid email addresses? Please advise THanks By valid, do you mean that the email is in proper syntax or that it is an actual email address. Text formulas can be used for proper syntax. Perhaps a Visual Basic proceedure. I doubt you can check that the email address actually exists, as many companies and ISPs will simply disregard and drop mail to invalid email addresses to eliminate spam "user" wrote: > Hi, > > How do you use Regular expression ...