Changing the colors of the specific dots in scatterplot vba excel
35,430
Get rid of the .Solid
or put this before you set the Forecolor.RGB
, I think that is overriding something (I have noticed this and some other bugs/inabilities to perform certain actions even as the Macro Recorder would make it seem these methods should work properly).
Option Explicit
Sub Kolorowanie()
Dim cht As Chart
Dim srs As Series
Dim pt As Point
Set cht = ActiveSheet.ChartObjects(1).Chart
Set srs = cht.SeriesCollection(1)
For Each pt In srs.Points
With pt.Format.Fill
.Visible = msoTrue
'.Solid 'I commented this out, but you can un-comment and it should still work
.ForeColor.RGB = RGB(255, 0, 0)
End With
Next
End Sub
Or:
For Each pt In srs.Points
With pt.Format.Fill
.Visible = msoTrue
.Solid 'This is the default so including it doesn't do anything, but it should work either way.
.ForeColor.RGB = RGB(255, 0, 0)
End With
Next
Applying this to your code gives us:
Sub Kolorowanie()
ActiveSheet.ChartObjects("Chart 1").Activate
a = ActiveChart.SeriesCollection(1).Values
b = ActiveChart.SeriesCollection(1).XValues
For i = LBound(a) To UBound(a)
If a(i) < 0 And b(i) > 0 Then
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SeriesCollection(1).Select
ActiveChart.SeriesCollection(1).Points(i).Select
With Selection.Format.Fill
.Visible = msoTrue
.Solid
.ForeColor.RGB = RGB(255, 0, 0)
End With
Else
End If
Next i
End Sub
Author by
haver24
Updated on February 05, 2020Comments
-
haver24 over 4 years
I would like to change the color of dots in top left part of the scatterplot. I wrote a macro, no errors occurred but the color did not change :/
Sub Kolorowanie() ActiveSheet.ChartObjects("Chart 1").Activate a = ActiveChart.SeriesCollection(1).Values b = ActiveChart.SeriesCollection(1).XValues For i = LBound(a) To UBound(a) If a(i) < 0 And b(i) > 0 Then ActiveSheet.ChartObjects("Chart 1").Activate ActiveChart.SeriesCollection(1).Select ActiveChart.SeriesCollection(1).Points(i).Select With Selection.Format.Fill .Visible = msoTrue .ForeColor.RGB = RGB(255, 0, 0) .Solid End With Else End If Next i End Sub
Any ideas why it does not work?