How do I programmatically change the line colour for a series in a chart in Excel 2007

Question

I have a chart with a series that denotes a large set (1000's) of discrete measurements. Some of these are bad measurements and I want to colour the line for the series based on another set of data that describes how accurate the measurements are. Bad measurements should be red and good measurements green and the in between on some kind of gradient from red to yellow to green.

This should be able to be programmed with VBA however I have no idea what to do. Can anyone give me some hints?

Answer 1

Are you locked into VBA? One way this could be accomplished is by open up your OOXML .xlsx document archive (it is actually a Zip archive). Then you have free access to the XML data that makes up the document itself. This could be ran through an XSL style sheet or any other script of your choosing and then rezipped.

Answer 2

It is easy enough to colour a chart line in VBA. Here are some notes.

Dim cht  As Chart
Dim sc As Series
Dim blnBad As Boolean
Dim j

j = 85 'RGB orange '
blnBad = False

'This is a chart called Chart 1, it would be possible '
'to use the charts collection '
Set cht = ActiveSheet.ChartObjects("Chart 1").Chart
'A chart is composed of series of data ... '
For Each sc In cht.SeriesCollection
    ' ... that you can iterate through to pick up '
    ' the individual data values, or a data range. '
    ' Values in this case. '
    For i = LBound(sc.Values) To UBound(sc.Values)
        ' That can be checked against another set of '
        ' values in the range Bad. '
        With ActiveSheet.Range("Bad")
            ' So, look for the value ... '
            Set c = .Find(sc.Values(i), lookat:=xlWhole, LookIn:=xlValues)
            ' and if it is found ... '
            If Not c Is Nothing Then
                ' ... then set the Bad flag '
                blnBad = True
            End If
        End With
    Next
    ' So, this range contains a Bad value '
    ' and we will colour it red ... '
    If blnBad Then
        sc.Border.Color = RGB(255, 0, 0)
        ' ... not forgetting the markers '
        sc.MarkerForegroundColor = RGB(255, 0, 0)
    Else
        ' Otherwise, use an increasingly yellow colour '
        sc.Border.Color = RGB(255, j, 0)
        sc.MarkerForegroundColor = RGB(255, j, 0)

        j = j + 30 ' getting more yellow
        ' Debug.Print j ' uncomment to see j in the immediate window '
    End If
    blnBad = False
Next
End Sub