Changing colour of a chart series based on conditions using vba

Question

I am trying to code using vba so that a series on a chart is formatted based on certain criteria. My coding is as follows

Sub CreateChart()
Dim NPOINTS As Integer
Dim NVAL(1000) As Range, XVAL(1000) As Range, YVAL(1000) As Range
Dim Score(1000) As Range
Sheets("Scenario").Select
Range("B4").Select

NPOINTS = Worksheets("Scenario").Range(Selection, Selection.End(xlDown)).Rows.Count
Set Scenario = Worksheets("Scenario")
ActiveSheet.Shapes.AddChart2(240, xlXYScatter).Select

 NVAL0 = "B3"
 XVAL0 = "C3"
 YVAL0 = "D3"
 SCORE0 = "E3"
 For i = 1 To NPOINTS
       Set Score(i) = Cells(Range(SCORE0).Offset(i, 0).Row, Range(SCORE0).Column)
       Set NVAL(i) = Cells(Range(NVAL0).Offset(i, 0).Row, Range(NVAL0).Column)
       Set XVAL(i) = Cells(Range(XVAL0).Offset(i, 0).Row, Range(XVAL0).Column)
       Set YVAL(i) = Cells(Range(YVAL0).Offset(i, 0).Row, Range(YVAL0).Column)
       Scorei = Score(i).Value
       ActiveChart.SeriesCollection.NewSeries
       ActiveChart.FullSeriesCollection(i).Name = NVAL(i)
       ActiveChart.FullSeriesCollection(i).XValues = XVAL(i)
       ActiveChart.FullSeriesCollection(i).Values = YVAL(i)
       If Scorei <= 10 >= 0 Then
          ActiveChart.SeriesCollection(i).Points.Interior.Colour = _
             RGB(0, 255, 0) 'Green
       ElseIf Scorei <= 30 >= 11 Then
          ActiveChart.SeriesCollection(i).Points.Interior.Colour = _
             RGB(0, 255, 0) 'Green
       ElseIf Scorei <= 60 >= 31 Then
          ActiveChart.SeriesCollection(i).Points.Interior.Colour = _
             RGB(0, 255, 0) 'Green
       ElseIf Scorei <= 100 >= 61 Then
          ActiveChart.SeriesCollection(i).Points.Interior.Colour = _
             RGB(0, 255, 0) 'Green
       Else
          MsgBox "ERROR :- Score out of range"
       End If


 Next
 With ActiveChart
    'chart name
   .HasTitle = False

    'X axis name
   .Axes(xlCategory, xlPrimary).HasTitle = True
   .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "INFLUENCE"
    'y-axis name
   .Axes(xlValue, xlPrimary).HasTitle = True
   .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "IMPORTANCE"
   .SetElement (msoElementLegendRight)
   .Location Where:=xlLocationAsNewSheet, Name:="Priority Chart"
End With
End Sub

Unfortunately when I run it, it fails with "Object doesn't support this property or method and then when I press Debug it highlights the following line

ActiveChart.SeriesCollection(i).Points.Interior.Colour = _
             RGB(0, 255, 0) 'Green

Where am I going wrong? I appreciate any help.

score 1 · Answer 1 · answered Dec 01 '17 at 11:41

1

just

ActiveChart.SeriesCollection(i).format.Fill.ForeColor.RGB =RGB(0, 255, 0)

answered Dec 01 '17 at 11:41

MarcinSzaleniec

2,246
1
7
22

when I do this, It only seems to affect the border of the data point. Any way of making this work to fill it? – OB1 Dec 01 '17 at 14:01
what kind of chart? – MarcinSzaleniec Dec 01 '17 at 14:06
It is a scatter chart (xlXYScatter) – OB1 Dec 01 '17 at 14:08
this above should work for fill. If you want it affect also a border ActiveChart.SeriesCollection(1).format.fill.ForeColor.RGB = – MarcinSzaleniec Dec 01 '17 at 14:24

Luuklag · Answer 2 · 2017-12-12T06:31:23.303

0

The .Points() is a collection. You will need to cycle through all its elements and change the color one by one. The left most point is .Points(1), the right most point is .Points.count as per: Change the Point Color in chart excel VBA

Also there is no such thing as interior colour for points. There are 4 relevant options as per: https://msdn.microsoft.com/en-us/vba/excel-vba/articles/point-object-excel

MarkerBackgroundColor
MarkerBackgroundColorIndex
MarkerForegroundColor
MarkerForegroundColorIndex

As per comment from Jon Peltier it is not recommended to make use of the colorindex as this is a legacy from excel <2003

edited Dec 12 '17 at 06:31

answered Dec 01 '17 at 11:39

Luuklag

3,897
11
38
57

Don't use the `ColorIndex` properties, stick to the `Color` properties. `ColorIndex` is legacy from Excel 2003 and earlier, and merely confuses the Excel color system even further. – Jon Peltier Dec 12 '17 at 04:43

Changing colour of a chart series based on conditions using vba

2 Answers2