Excel 2007 Negative Bar Colors in a Chart
 

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

Excel 2007 Negative Bar Colors in a Chart
 

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/ChartsH...fNegative.html

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

--
John Mansfield
http://cellmatrix.net





"djdubya" wrote:

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

Excel 2007 Negative Bar Colors in a Chart
 

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