Change Line Color Of Graph In Excel Using VBA

[JohnCaver]

Hi, I'm trying to programatically change the color of lines in a line graph using a VBA macro in Excel. Is this actually possible?

 

[xlbo]

yup - record yourself doing so to get the basic code - then post back here with how you want to achieve this functionality - ie on what logical condition will the colour of the cxhart line be set ?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[JohnCaver]

Yes I tried that - unfortuantely, for reasons best known to Microsoft, the color change does not get recorded.

I have since discovered you can do it by setting the ColorIndex property of the associated Border object.

 

[SkipVought]

Did you actually CHANGE the selected color from what it was, to a DIFFERENT color.

If you merely go thru the change process WITHOUT ACTUALLY CHANGING THE COLOR, no code is generated for color change.

Skip,

_[red][/red]

 

[JohnCaver]

I did change the color.

 

[SkipVought]

Then post the code that was generated, regardless.

Skip,

_[red][/red]

 

[xlbo]

Really? I just tried it and got this:

ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.SeriesCollection(1).Select
    With Selection.Border
        [b].ColorIndex = 3[/b]
        .Weight = xlThin
        .LineStyle = xlContinuous
    End With
    With Selection
        .MarkerBackgroundColorIndex = xlAutomatic
        .MarkerForegroundColorIndex = xlAutomatic
        .MarkerStyle = xlAutomatic
        .Smooth = False
        .MarkerSize = 5
        .Shadow = False
    End With

The Border colour IS the line colour - a border is after all just a line....

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[JohnCaver]

I guess you are not using Excel 2007?

 

[SkipVought]

Pleeeeezzzzzzzzz post your code.

Skip,

_[red][/red]

 

[JohnCaver]

It just records the selecting:

    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.SeriesCollection(2).Select

 

[SkipVought]

...and you cannot take Geoff's code and make it work?

Skip,

_[red][/red]

 

[JohnCaver]

Yes I did, read my 2nd post

I have since discovered you can do it by setting the ColorIndex property of the associated Border object.

It will still be interesting to see if this is an issue with Excel 2007.

 

[xlbo]

interesting - you are correct - I am using 2003

From the sounds of it (I have heard of plenty of other "anomolies"), I don't want to "upgrade" to 2007 !!

Also interesting to note that the method for changing the colur has not changed (ref the border property) but yet the recording does not seem to work. Can I ask if you have noticed any other items that don't seem to record in 2007 ?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Asspin]

A couple of thoughts.

With ActiveSheet
     .SeriesCollection(1).Border.ColorIndex = 57 'Change color of line on line graph
     .PlotArea.Interior.ColorIndex = 19 ' Change color of graph area
End With

I use both of these all the time in Excel 2003 and have no problem.

Dan

 

[Asspin]

That should be ActiveChart, not sheet, sorry!

Dan