Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
see… – SeanC Jun 21 '12 at 16:14
up vote 0 down vote accepted

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
        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.

share|improve this answer

Instead of using a macro, make two series. Suppose the grades are in B2:B10. Select C2:C10 with C2 the active cell. Type:


then hold Ctrl and press Enter, which enters the formula in all cells of the selected range.

Select D2:D10 with D2 the active cell. Type


then hold Ctrl and press Enter.

The value from column B will show up only once, in column C or D, based on its value, and the other column will contain the #N/A error, which is not plotted with a marker.

Make your chart with columns C and D, format C with the good color and D with the bad color. Red and green are not a good choice because of the predominance of red-green colorblindness. Try blue for good and orange for bad.

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .