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

JohnnyC · External Since: Jul 02, 2009 Posts: 2

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

Shane Devenshire · External Since: Jan 07, 2009 Posts: 252

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?

Shane Devenshire · External Since: Jan 07, 2009 Posts: 252

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?

JohnnyC · External Since: Jul 02, 2009 Posts: 2

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?