How can I vary the marker colors in an Excel graph?

Question

I have an Excel line graph with markers.

I need to change the color of the marker if it is below a threshold.
For example, the graph below is monthly results where the markers are above a threshold. If the results are below a certain point, I need the marker to change color (2nd graph).

I know how to change the marker colors manually. Can this be done automatically? Thank you, in advance, for your help.

This can be done with VBA: `MyChart.FullSeriesCollection(x).Points(y).Format.Fill.ForeColor.RGB = RGB(255, 0, 0)` where `x` is the series collection and `y` is the point in the collection you want to change — cybernetic.nomad, Sep 25 '18 at 19:25

Dy.Lee · Answer 1 · 2018-10-11T02:25:48.123

test bellow.

Sub setMarkerColor()
    Dim myCht As ChartObject
    Dim n  As Long
    Dim adr As String
    Dim p As Double

    Application.ScreenUpdating = False
    For Each myCht In ActiveSheet.ChartObjects
        With myCht.Chart
            adr = .SeriesCollection(1).Formula '<~~ First grapth, if 2nd change to .SeriesCollection(2).Formula
            adr = Split(adr, ",")(2)
            For n = 1 To Range(adr).Cells.Count
               p = Range(adr)(n)
               If p < 15 Then '<~~ set value 15
                   '.SeriesCollection(1).Points(n).HasDataLabel = True
                   With .SeriesCollection(1).Points(n) '<~~ First grapth, if 2nd change to .SeriesCollection(2).Points(n)
                    .MarkerForegroundColor = RGB(0, 255, 0)
                    .MarkerBackgroundColor = RGB(255, 0, 0)
                    End With
               End If
            Next n
        End With
    Next myCht
    Application.ScreenUpdating = True
 End Sub

edition

Sub setMarkerColor()
    Dim myCht As ChartObject
    Dim n  As Long
    Dim adr As String
    Dim p As Double
    Dim myVal As Long
    Application.ScreenUpdating = False
    For Each myCht In ActiveSheet.ChartObjects
        With myCht.Chart
            adr = .SeriesCollection(1).Formula '<~~ First grapth, if 2nd change to .SeriesCollection(2).Formula
            adr = Split(adr, ",")(2)
            For n = 1 To Range(adr).Cells.Count
               p = Range(adr)(n)
               If p >= 0.99 Then '<~~ set value 0.99
                    myVal = RGB(0, 255, 0)
                Else
                    myVal = RGB(255, 0, 0)
                End If
                   '.SeriesCollection(1).Points(n).HasDataLabel = True
                   With .SeriesCollection(1).Points(n) '<~~ First grapth, if 2nd change to .SeriesCollection(2).Points(n)
                    .MarkerForegroundColor = RGB(0, 255, 0)
                    .MarkerBackgroundColor = myVal
                    End With

            Next n
        End With
    Next myCht
    Application.ScreenUpdating = True
 End Sub

Thank you Dy.Lee, I ran the code you provided and keep getting a "Compile error: Invalid outside procedure", so I'm not clear what I need to edit. — H.Cani, Oct 08 '18 at 14:59
My apologies, I modified the code that resulted in that error. I corrected it now, so thank you for that. — H.Cani, Oct 08 '18 at 18:54
One last question: Within the code, how do I specify that I want to color change to occur if the marker is below a threshold? For example, the chart is based on the following results: 99.6%, 99.9%, 99.5%, 99.5%, 99.9% and 99.7%. If the results are above 99.0%, the dots (markers) stay green. If they fall below 99.0% (98%, for example), the dot (marker) should turn red. How do I make this modification? — H.Cani, Oct 08 '18 at 18:54

How can I vary the marker colors in an Excel graph?

1 Answers1