Geek asks and answers

How to programmatically change the line color for a series in a chart in Excel 2007

I have a chart with a series that represents a large set of (1000) discrete measurements. Some of them are poor measurements, and I want to colorize the line for the series based on another dataset that describes how accurate the measurements are. Bad measurements should be red, and good measurements should be green, and an intermediate should be from some kind of gradient from red to yellow to green.

This can be programmed using VBA, but I do not know what to do. Can someone give me some advice?

+2
source share
2 answers

Just paint the line chart in VBA. Here are a few 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
+4
source

VBA? , , - OXXML.xlsx( Zip-). XML-, . XSL script , .

+1

More articles:


All Articles