Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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
1  

2 Answers 2

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

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:

=IF(B2>=90,B2,NA())

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

=IF(B2<90,B2,NA())

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.