Change chart marker color if there is continuous value higher than a baseline value

Question

I am currently creating a VBA macro to change the color of the marker of a chart if the value in the chart consists of 3 continuous spikes that exceeds a baseline value of 0.7.

For example, in the picture below, I've create a macro to change all the marker colors to red if the value is higher than the baseline value, but not if there are 3 continuous values higher than baseline value.

My Code

This is what I've tried - changing the marker color to red if the value exceeds 0.7

Sub Tester()

Dim cht As Chart, s As Series, p As point
Dim vals, x As Integer

    Set cht = ThisWorkbook.Worksheets("mySheet3").ChartObjects("Chart 1").Chart
    Set s = cht.SeriesCollection(1)
    vals = s.Values
    
    For x = LBound(vals) To UBound(vals)
      If vals(x) > 0.7 Then
        With s.Points(x)
            .MarkerBackgroundColor = RGB(255, 0, 0)
            .MarkerForegroundColor = RGB(255, 0, 0)
        End With
      End If
    Next x
    
End Sub

user_3219613 · Answer

For that you would need to work with a window size to check always 3 dots in a row if they are above basline if the are color them and move 1 further to check the next 3 in a row.

Option Explicit

Sub Tester()
    Dim cht As Chart
    Set cht = ThisWorkbook.Worksheets("mySheet3").ChartObjects("Chart 1").Chart
    
    Dim s As Series
    Set s = cht.SeriesCollection(1)
    
    Dim vals As Variant
    vals = s.Values
    
    Const WindowSize As Long = 3
    Dim Colorize As Boolean
    
    Dim x As Long
    For x = LBound(vals) To UBound(vals)
        If x + WindowSize - 1 <= UBound(vals) Then
            Colorize = True
            Dim w As Long
            For w = x To x + WindowSize - 1
                If Not vals(w) > 0.7 Then
                    Colorize = False
                    Exit For
                End If
            Next w
            If Colorize Then
                For w = x To x + WindowSize - 1
                    With s.Points(w)
                        .MarkerBackgroundColor = RGB(255, 0, 0)
                        .MarkerForegroundColor = RGB(255, 0, 0)
                    End With
                Next w
             End If
        End If
    Next x
End Sub

Change chart marker color if there is continuous value higher than a baseline value

1 Answers