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

[JohnnyC]

Is there a macro command that will assign a user specifide color to each
individual line of a scatter graph?

 

[Shane Devenshire]

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?

 

[Shane Devenshire]

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.

 

[JohnnyC]

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