Change the Point Color in chart excel VBA

Question

I'm trying to the get the points from a chart in excel vba?

I made a chart of student marks in excel, and I want to put a condition i.e if marks on chart are greater then 90 then the point color changes to red and if less 90 then color is green. How can I do this using VBA?

Solution

Here is a quick example that should give you everything you need:

Sub Macro1()
    Dim crt As Chart
    Dim ser As Series
    Dim val As Long
    Dim pnt As Point

    Set crt = ActiveChart

    Debug.Print "Number of Series: ", crt.SeriesCollection.Count

    Set ser = crt.SeriesCollection(2)

    Debug.Print "# Points in Series 2: ", ser.Points.Count
    NumPoints = ser.Points.Count

    Debug.Print "Values for Series 2:"
    For i = 1 To NumPoints
        val = ser.Values(i)
        Debug.Print " ", i, val,

        Set pnt = ser.Points(i)

        If val > 6 Then
            pnt.MarkerForegroundColorIndex = 4
            pnt.MarkerBackgroundColorIndex = 4
            Debug.Print pnt.MarkerForegroundColor, pnt.MarkerForegroundColorIndex
        Else
            Debug.Print
        End If
    Next i

End Sub

You should set the colour using the Index value if you can as this retains consistent colours. Also note that the non-index colour number starts off as -1 so you can't use it to check the actual colour value. It is set, however, once you change the colour. You will also want to change the background colour in addition to the Foreground if you want the fill to be the same as the edge.

Comments

[SeanC]

see stackoverflow.com/questions/11136194/…