I wrote the following code which solves the problem for us.
Assign the following code to a keypress, select the chart to update and run
the code.
Sub ChartFormatting()
'Written 15 Oct 2007
'Routine to reformat graphs in Excel 2007 to set positive value to be colour
1 i.e. blue and negative values to be colour 2 i.e. red
'Declare variables
Dim i As Integer
Dim DataPoints As Integer
Dim X As Object
Dim XValuesArray
Dim ChartCheck As Boolean
Dim PositiveFill, NegativeFill As Integer
Dim ChartType1, ChartType2 As Integer
'Set Values
PositiveFill = 32 'i.e. blue fill for positives
NegativeFill = 3 'i.e. Red fill for negatives
ChartType1 = 51 'i.e. a basic column chart
ChartType2 = 57 'i.e. a basic bar chart
'Check if we have a chart selected, calls ChartIsSelected function
If Not ChartIsSelected Then
Exit Sub
End If
' Calculate the number of data points.
DataPoints = UBound(ActiveChart.SeriesCollection(1).Values)
For Each X In ActiveChart.SeriesCollection
'Set array size to contain graph point values
ReDim XValuesArray(1, DataPoints)
'Read in values from graph and store in array
XValuesArray = X.Values
'Checks the chart type to see if this is a basic bar chart or column
chart series, otherwise ignores
ChartCheck = X.ChartType = ChartType1 Or X.ChartType = ChartType2
If ChartCheck Then
'For each value in chart series
For i = 1 To UBound(XValuesArray)
'Format if positive value
If XValuesArray(i) >= 0 Then
X.Points(i).Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlNone
End With
Selection.Shadow = False
Selection.InvertIfNegative = False
With Selection.Interior
.ColorIndex = PositiveFill
.Pattern = xlSolid
End With
Else
'Format if negative value
X.Points(i).Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlNone
End With
Selection.Shadow = False
Selection.InvertIfNegative = False
With Selection.Interior
.ColorIndex = NegativeFill
.Pattern = xlSolid
End With
End If
Next i
End If
'Deselect chart
ActiveChart.Deselect
Next
End Sub
Private Function ChartIsSelected() As Boolean
'ChartIsSelected function, returns true if we have selected a chart or
embedded chart option, otherwise false
ChartIsSelected = Not ActiveChart Is Nothing
End Function
Hopefully this does what everyone is looking for.
Cheers
Gareth
