Excel 2007 Negative Bar Colors in a Chart

[Guest]

I have a bar chart that is updated on a regualr basis. My problem is that
when the bar goes negative, I can't get the bar color to go red, as I could
in previous Excel versions. Is there something I'm missing in the formating
window? I've tried the invert if negative but that just makes the bar
without fill.
Thanks

 

[Guest]

One option - Andy Pope's Pattern Fills Add-In can help you get around the
problem:

http://www.andypope.info/charts/patternfills.htm

Another option is to refer to Jon's example:

http://peltiertech.com/Excel/ChartsHowTo/InvertIfNegative.html

Finally, you could use two different series instead of one i.e. one for
positive values and one for negative values.

 

[Guest]

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