Geek Answers Handbook

Excel VBA line color / marker line colors

I am writing VBA code to modify Excel charts. For a scatterplot, I need to change the color of the marker line, and sometimes the color of the line of the connecting lines. I can do it manually, but when I record a macro, both actions lead to the same code, even though the results are very different.

Any idea how to distinguish between line color and marker line color in code?

This code was created when I wrote me down, changing the color of marker lines

Sub Macro3() ' ' Macro3 Macro ' ' ActiveChart.SeriesCollection(2).Select With Selection.Format.Line .Visible = msoTrue .ForeColor.ObjectThemeColor = msoThemeColorAccent1 .ForeColor.TintAndShade = 0 .ForeColor.Brightness = 0 End With End Sub

This code was created when I wrote it myself, changing the color of the line connecting the markers

 Sub Macro4() ' ' Macro4 Macro ' ' 'Change the Line Color ActiveChart.SeriesCollection(2).Select With Selection.Format.Line .Visible = msoTrue .ForeColor.ObjectThemeColor = msoThemeColorAccent1 .ForeColor.TintAndShade = 0 .ForeColor.Brightness = 0 End With End Sub
+5
source share
3 answers

Series.Format.Line.ForeColor Line Color Series.Format.Line.ForeColor . Marker line color Series.MarkerForegroundColor . But at least with Excel 2007 there is a problem with setting Series.Format.Line.ForeColor . Example:

 Sub Macro3() Dim oChart As Chart Dim oSeries As Series Set oChart = ActiveChart Set oSeries = oChart.SeriesCollection(2) oSeries.Format.Line.Weight = 5 'Line.Weigth works ever oSeries.Format.Line.Visible = msoFalse 'for Line.ForeColor getting to work we have to cheat something oSeries.Format.Line.Visible = msoTrue oSeries.Format.Line.ForeColor.RGB = RGB(0, 255, 0) 'now it works oSeries.MarkerSize = 15 oSeries.MarkerBackgroundColor = RGB(255, 0, 0) 'marker background oSeries.MarkerForegroundColor = RGB(0, 0, 255) 'marker foreground (lines around) End Sub

ActiveChart is a scatter chart. And this is verified using Excel 2007.

+11
source

In Excel 2013, the line color and the marker line color are easy to distinguish, since the line color is set using the .Border property, and the marker colors are set using . MarkerBackgroundColor and .MarkerForegroundColor .

So, the following will give you white markers with a red border and black connecting lines between them:

 ActiveChart.SeriesCollection(1).Select With Selection .Border.LineStyle = xlContinuous .Border.Color = RGB(0,0,0) .MarkerBackgroundColor = RGB(255, 255, 255) .MarkerForegroundColor = RGB(255, 0, 0) End With

Note. If you use Selection.Format.Line.Weight , note that this applies to both the borders and the default thickness of the connector

+2
source

you can use

ActiveChart.SeriesCollection (1) .MarkerForegroundColor = -2

0
source

More articles:


All Articles