Excel VBA Color code XY scatter plot by third dimension

Question

First off, I'm a complete newbie at VBA. I'm trying to write a macro that will color code an XY scatter plot with multiple series with a third dimension see spreadsheet. For this, I've given each cell in a particular column (in my case, column I) a particular background colour. Now, I've managed to get the code to work with a single series single series color. However, when I add in more than one series, it restarts the coloring again with the first cell in the series rather than moving on to the correct cell see this image with the wrong colors. For example, if series two starts in row 53, I'd need the color of cell I53 to be assigned to the XY in row 53. Instead, it restarts with the first available color (cell I2). What am I missing here?

Sub Colorpoints()

Dim cht As Chart
Dim ser As Series
Dim pnt As Point
Dim i As Long, j As Long, k As Long
Dim rng As Range
Set cht = ActiveChart
Set ser = cht.SeriesCollection(1)
Set rng = ActiveSheet.Range("I:I") ' Each of these cells has a different color

For k = 1 To cht.SeriesCollection.Count
Set ser = cht.SeriesCollection(k)

j = 0

For i = 1 To ser.Points.Count

    j = j + 1

    Set pnt = ser.Points(i)

    pnt.MarkerBackgroundColor = rng(j).Interior.Color ' Cycle through available colors

    If (j > rng.Count) Then j = 0

Next i
Next k

End Sub

add some relevant screenshots to let people understand your data layout and XY plot — DisplayName

Shelty Shelty · Accepted Answer · 2018-09-23T16:26:24

I added some indentation and you can now clearly see where your mistake is. That statement j = 0 is unnecessary. If you want to use it then put it before For k = 1 To cht.SeriesCollection.Count. Use proper indentation and you will avoid a lot of mistakes I guess.

Corrected code:

Option Explicit

Sub Colorpoints()

    Dim cht As Chart
    Dim ser As Series
    Dim pnt As Point
    Dim i As Long, j As Long, k As Long
    Dim rng As Range

    Set cht = ActiveChart
    Set ser = cht.SeriesCollection(1)
    Set rng = ActiveSheet.Range("I:I") ' Each of these cells has a different color

    For k = 1 To cht.SeriesCollection.Count

        Set ser = cht.SeriesCollection(k)

        'j = 0  'If you set it to 0 when stepping into another series
                'then obviously it starts colouring from the beginning
                'Just remove it

        For i = 1 To ser.Points.Count

            j = j + 1

            Set pnt = ser.Points(i)

            pnt.MarkerBackgroundColor = rng(j).Interior.Color ' Cycle through available colors

            If (j > rng.Count) Then j = 0

        Next i
    Next k

End Sub

Excel VBA Color code XY scatter plot by third dimension

1 Answers