graphing find the X,Y of a point

[par60056]

Somebody asked me to help them with a graph. I know very little about graphing.

They have the following table:

x	y	size	color
4	5	1	5
2	7	1	3
3	9	2	7

<tbody>
</tbody>

They created a scatter chart(actually 100s of dots). Now they are asking if I can create some VBA to change the color of each dot in the scatter chart.

I wanted to try to index through the points and look up the color on the raw data but I cannot find the original x,y values in the point object to then use to lookup on the raw data.

Any suggestions?

 

[Andrew Poulsom]

See if you can adapt this:

Sub Test()
    Dim i As Long
    With ActiveChart
        With .SeriesCollection(1)
            For i = 1 To .Points.Count
                .Points(i).MarkerBackgroundColorIndex = ActiveSheet.Range("D2:D4").Cells(i, 1).Value
                .Points(i).MarkerForegroundColorIndex = ActiveSheet.Range("D2:D4").Cells(i, 1).Value
            Next i
        End With
    End With
End Sub

 

[par60056]

That was my fall back. I was hoping that I could get the X,Y of the point from the series (something like .Points(i).xValue) But I guess simply assuming the point order is right is my only option.

 

[Andrew Poulsom]

The points will be in the right order. You can get the source range for the Series like this:

Sub Test()
    Dim Rng As Range
    Dim i As Long
    With ActiveChart
        With .SeriesCollection(1)
            Set Rng = Range(Split(.Formula, ",")(2))
            For i = 1 To .Points.Count
                .Points(i).MarkerBackgroundColorIndex = Rng.Cells(i, 3).Value
                .Points(i).MarkerForegroundColorIndex = Rng.Cells(i, 3).Value
            Next i
        End With
    End With
End Sub

 

[par60056]

Thank you. As long as I am assured that the points will be in order I'm not as worried.

Thank you for the .SeriesCollection(1).Formula That lets me find it if he moves the data. I'll feel much more comfortable having asked the series where it's data is.