Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Change Line Color Of Graph In Excel Using VBA

Status
Not open for further replies.

JohnCaver

Programmer
May 3, 2007
10
GB
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?
 
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
 
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.
 




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,

[glasses] [red][/red]
[tongue]
 
Really? I just tried it and got this:
Code:
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
 
It just records the selecting:

Code:
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.SeriesCollection(2).Select
 
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.
 
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
 
A couple of thoughts.

Code:
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top