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

here a vba function I use to invert negative bars so they are red. Maybe this can be adapted:

The function is called from a sub routine in the a module in the workbook like this:

Sub FixTheGraph()
  FormatGraph("Overview")
End Sub

Here's the Function. You can just paste it below the sub-routine:

Function FormatGraph(myWorksheet As String)

Excel.Sheets(myWorksheet).Select

Dim myChartObject As ChartObject
For Each myChartObject In Excel.ActiveSheet.ChartObjects
    myChartObject.Select

    Dim myPoint As Integer, valArray

    With Excel.ActiveChart.SeriesCollection(1)
        valArray = .Values
        For myPoint = 1 To .Points.Count
            'myVal = valArray(myPoint)
            Select Case valArray(myPoint)
                Case 0.9 To 1
                    With .Points(myPoint)
                        .Interior.ColorIndex = 1 '<change colour to suit
                    End With
                Case 0.7 To 0.9
                    With .Points(myPoint)
                        .Interior.ColorIndex = 5 '<change colour to suit
                    End With
                Case Is < 0.7
                    With .Points(myPoint)
                        .Interior.ColorIndex = 3
                    End With
                End Select
        Next
    End With

Next myChartObject
Excel.Sheets(myWorksheet).Cells(1, 1).Select

End Function

Here's a simple little routine for your first question, recoloring the series in your chart blue, orange, and gray but reverse the default order:

Sub ReverseDefaultColors()
  Dim iSrs As Long, nsrs As Long

  If ActiveChart Is Nothing Then
    MsgBox "Select a chart and try again.", vbExclamation, "No Active Chart"
  Else
    With ActiveChart
      nsrs = .SeriesCollection.Count
      ' work backwards from last series
      For iSrs = nsrs To 1 Step -1
        Select Case nsrs - iSrs
          Case 0 ' last series
            .SeriesCollection(iSrs).Format.Fill.ForeColor.ObjectThemeColor = _
                msoThemeColorAccent1
          Case 1 ' next to last series
            .SeriesCollection(iSrs).Format.Fill.ForeColor.ObjectThemeColor = _
                msoThemeColorAccent2
          Case 2 ' etc.
            .SeriesCollection(iSrs).Format.Fill.ForeColor.ObjectThemeColor = _
                msoThemeColorAccent3
        End Select
      Next
    End With
  End If
End Sub

Here's another for your second question, coloring green, light green, and red based on series name (adjust RGB as required). You should note that some people (about 8% of males, less than 1% of females) may have problems distinguishing between green and red. For this reason, blue and orange are often used as a preferred color scheme.

Sub ColorGreenToRed()
  Dim iSrs As Long, nSrs As Long
  If ActiveChart Is Nothing Then
    MsgBox "Select a chart and try again.", vbExclamation, "No Active Chart"
  Else
    With ActiveChart
      nSrs = .SeriesCollection.Count
      For iSrs = 1 To nSrs
        ' only format series whose names are found
        Select Case LCase$(.SeriesCollection(iSrs).Name)
          Case "on time"
            .SeriesCollection(iSrs).Format.Fill.ForeColor.RGB = _
                RGB(0, 176, 80) ' Green
          Case "in tolerance"
            .SeriesCollection(iSrs).Format.Fill.ForeColor.RGB = _
                RGB(146, 208, 80) ' Light Green
          Case "late"
            .SeriesCollection(iSrs).Format.Fill.ForeColor.RGB = _
                RGB(255, 0, 0) ' Red
        End Select
      Next
    End With
  End If
End Sub