Discover MakerZone

MATLAB and Simulink resources for Arduino, LEGO, and Raspberry Pi

Learn more

Discover what MATLAB® can do for your career.

Opportunities for recent engineering grads.

Apply Today

Thread Subject:
EXCEL Series Line Colors

Subject: EXCEL Series Line Colors

From: Mike

Date: 15 Jul, 2010 14:39:05

Message: 1 of 15

The problem I'm having is that I am trying to change the color of one of my series in an excel chart that I have plotted as a xlXYScatterSmooth. To clarify I want the color of the LINE to be changed and not the markers~

so I know that the macro that VB produces gives

ActiveChart.SeriesCollection(9).Select
    With Selection.Border
        .ColorIndex = 1
        .Weight = xlThick
        .LineStyle = xlContinuous
    End With

But I can't just do:

.SeriesCollection(9).Border.ColorIndex = 1

as that does not work

Not really sure on what to do to get the color to change~

Thanks

Subject: EXCEL Series Line Colors

From: Yair Altman

Date: 15 Jul, 2010 17:33:22

Message: 2 of 15

> But I can't just do:
>
> .SeriesCollection(9).Border.ColorIndex = 1
>
> as that does not work

.SeriesCollection.Item(9).Border.ColorIndex = 1

Yair Altman
http://UndocumentedMatlab.com

Subject: EXCEL Series Line Colors

From: Mike

Date: 15 Jul, 2010 17:52:04

Message: 3 of 15

"Yair Altman" <altmanyDEL@gmailDEL.comDEL> wrote in message <i1ngp2$4ma$1@fred.mathworks.com>...
> > But I can't just do:
> >
> > .SeriesCollection(9).Border.ColorIndex = 1
> >
> > as that does not work
>
> .SeriesCollection.Item(9).Border.ColorIndex = 1
>
> Yair Altman
> http://UndocumentedMatlab.com

Omg, ty so much :D

Subject: EXCEL Series Line Colors

From: Mike

Date: 21 Jul, 2010 11:40:04

Message: 4 of 15

"Mike " <mike87y@gmail.com> wrote in message <i1nhs4$evj$1@fred.mathworks.com>...
> "Yair Altman" <altmanyDEL@gmailDEL.comDEL> wrote in message <i1ngp2$4ma$1@fred.mathworks.com>...
> > > But I can't just do:
> > >
> > > .SeriesCollection(9).Border.ColorIndex = 1
> > >
> > > as that does not work
> >
> > .SeriesCollection.Item(9).Border.ColorIndex = 1
> >
> > Yair Altman
> > http://UndocumentedMatlab.com
>
> Omg, ty so much :D

I've realized that this works in Excel 2003, however when I try to do it in Excel 2007 it says it is not an appropriate method/property

Subject: EXCEL Series Line Colors

From: Mike

Date: 21 Jul, 2010 11:41:04

Message: 5 of 15

"Mike " <mike87y@gmail.com> wrote in message <i1nhs4$evj$1@fred.mathworks.com>...
> "Yair Altman" <altmanyDEL@gmailDEL.comDEL> wrote in message <i1ngp2$4ma$1@fred.mathworks.com>...
> > > But I can't just do:
> > >
> > > .SeriesCollection(9).Border.ColorIndex = 1
> > >
> > > as that does not work
> >
> > .SeriesCollection.Item(9).Border.ColorIndex = 1
> >
> > Yair Altman
> > http://UndocumentedMatlab.com
>
> Omg, ty so much :D

I've realized that this works in Excel 2003, however when I try to do it in Excel 2007 it says it is not an appropriate method/property

Subject: EXCEL Series Line Colors

From: Andy

Date: 21 Jul, 2010 12:56:05

Message: 6 of 15

> I've realized that this works in Excel 2003, however when I try to do it in Excel 2007 it says it is not an appropriate method/property

Are you certain that it's complaining about the Item method? Could you post the error message?

Item is listed as a method in the Excel 2007 Developer's Reference: http://msdn.microsoft.com/en-us/library/bb178917%28v=office.12%29.aspx

Subject: EXCEL Series Line Colors

From: Mike

Date: 21 Jul, 2010 17:05:08

Message: 7 of 15

sorry for the double post

This is what I get when I run through Excel 2007

??? No appropriate method, property, or field Border for class Interface.0002086B_0000_0000_C000_000000000046.

Error in ==> XXXXXXXXXXXX
    c.SeriesCollection.Item(k+1).Border.ColorIndex = 1;


Thanks~

Subject: EXCEL Series Line Colors

From: Andy

Date: 21 Jul, 2010 17:19:06

Message: 8 of 15

"Mike " <mike87y@gmail.com> wrote in message <i279c3$sqs$1@fred.mathworks.com>...
> sorry for the double post
>
> This is what I get when I run through Excel 2007
>
> ??? No appropriate method, property, or field Border for class Interface.0002086B_0000_0000_C000_000000000046.
>
> Error in ==> XXXXXXXXXXXX
> c.SeriesCollection.Item(k+1).Border.ColorIndex = 1;
>
>
> Thanks~

Well of course. Did you check the link to the developer's reference? The Item method of the SeriesCollection object returns a Series object. If you click the link to the reference page for the Series object, it has no methods or properties called Border. Have you tried, for example, the MarkerForegroundColor property?

Note: I can't test anything, because I don't have access to Excel 2007. But it's often helpful to record a macro in Excel while you perform the action you're trying to accomplish, and then look at the generated VB code.

Subject: EXCEL Series Line Colors

From: Mike

Date: 21 Jul, 2010 17:48:22

Message: 9 of 15

Yea...actually most of the methods I find are from VB macros that I try out. However Excel 2007 changed the way they do objects in charts for some stupid reason in a way that you can't even track the changes through a macro on VB~ (I forget where I read this, somewhere from the msdn site)

Also the marker foreground just refers to the markers on the plot itself and not the line that gets drawn through them to connect them to each other :/

I'm just wondering if there's some way to do it still for 2007

(I have to write these programs basically for both 2003 and 2007)

Subject: EXCEL Series Line Colors

From: Andy

Date: 21 Jul, 2010 18:01:16

Message: 10 of 15

"Mike " <mike87y@gmail.com> wrote in message <i27bt6$e39$1@fred.mathworks.com>...
> Yea...actually most of the methods I find are from VB macros that I try out. However Excel 2007 changed the way they do objects in charts for some stupid reason in a way that you can't even track the changes through a macro on VB~ (I forget where I read this, somewhere from the msdn site)
>
> Also the marker foreground just refers to the markers on the plot itself and not the line that gets drawn through them to connect them to each other :/
>
> I'm just wondering if there's some way to do it still for 2007
>
> (I have to write these programs basically for both 2003 and 2007)

As I said, I don't have access to Excel 2007. If there are major differences between them, then you're stuck going through the Developer Reference, using trial and error until you find the relevant object. If you need to handle both Excel 2003 and 2007 and they're very different, then there's not much you can do but write both sets of code and have your MATLAB program choose the correct version at run time.

As for this particular problem, have you looked at the TrendLine object? Something like:

SeriesCollection.Item(9).TrendLines(1).Border.Color

might be what you're looking for.

Subject: EXCEL Series Line Colors

From: james bejon

Date: 21 Jul, 2010 20:39:04

Message: 11 of 15

I don't have Excel 07 here. But I guess you've tried setting the

SeriesCollection.Item(i).Interior.ColorIndex

property? Does this just affect the markers or something?

Subject: EXCEL Series Line Colors

From: Mike

Date: 22 Jul, 2010 15:16:07

Message: 12 of 15

>Andy
Naw...not looking for trendlines~

>james
c.SeriesCollection.Item(#).Interior
gives me ??? No appropriate method,...etc

:(

Subject: EXCEL Series Line Colors

From: Andy

Date: 22 Jul, 2010 15:42:05

Message: 13 of 15

Try something like the following:

ActiveChart.ChartGroups(1).SeriesLines(1).Border.Color = 1;

or:

ActiveChart.ChartGroups(1).SeriesLines.Border.Color = 1;

or:

ActiveChart.ChartGroups(1).SeriesLines.Border.ColorIndex = 1;

Subject: EXCEL Series Line Colors

From: Mike

Date: 22 Jul, 2010 16:49:04

Message: 14 of 15

>Andy
tried those earlier~ didn't work either :(

Subject: EXCEL Series Line Colors

From: Andy

Date: 22 Jul, 2010 17:10:08

Message: 15 of 15

"Mike " <mike87y@gmail.com> wrote in message <i29sq0$ke$1@fred.mathworks.com>...
> >Andy
> tried those earlier~ didn't work either :(

Did you try variations, such as ChartGroups.Item(1) instead of ChartGroups(1)? Can you tell CSSM what didn't work (that is, show us the error messages)? Also, I can only do so much reading the Excel developer's reference and suggesting things that sound relevant. I don't have Excel 2007. So you'll need to go through the developer's reference yourself and use trial and error. If you find a solution, you should post it back here. And if you try several more things and still don't find a solution, you should post all of your attempts here along with error messages or undesirable output.

Tags for this Thread

What are tags?

A tag is like a keyword or category label associated with each thread. Tags make it easier for you to find threads of interest.

Anyone can tag a thread. Tags are public and visible to everyone.

Contact us