PC Review


Reply
Thread Tools Rate Thread

Excel 2007 Negative Bar Colors in a Chart

 
 
=?Utf-8?B?ZGpkdWJ5YQ==?=
Guest
Posts: n/a
 
      11th Oct 2007
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
 
Reply With Quote
 
 
 
 
=?Utf-8?B?Sm9obiBNYW5zZmllbGQ=?=
Guest
Posts: n/a
 
      11th Oct 2007
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

 
Reply With Quote
 
 
 
 
=?Utf-8?B?R0FSV0lM?=
Guest
Posts: n/a
 
      15th Oct 2007
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
 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
excel 2007 colors do not match excel 2003 colors. Tom's Travails' Microsoft Excel Misc 0 17th Jul 2008 01:56 AM
Changing color of negative bar when using Invert if negative Breck Microsoft Excel Charting 9 4th Mar 2008 01:40 PM
Re: How to display the negative bar in bar chart? Jon Peltier Microsoft Excel Charting 0 16th Dec 2005 06:40 AM
separate colors for positive and negative bars in bar charts? =?Utf-8?B?dCBraWxsaW9u?= Microsoft Excel Charting 1 26th Aug 2005 04:55 PM
Colors, Colors, and more colors =?Utf-8?B?Q2Fi?= Windows XP Internet Explorer 2 29th Feb 2004 02:30 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 03:45 PM.