Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to get 2003 colors in Excel 2007 | Excel Discussion (Misc queries) | |||
Excel 2007 backward compatibility re colors | New Users to Excel | |||
EXcel 2007 - Unable to sort by font and cell colors | Excel Worksheet Functions | |||
Excel 2007 Colors (Text, Fill) | Excel Discussion (Misc queries) | |||
separate colors for positive and negative bars in bar charts? | Charts and Charting in Excel |