Excel 2007 Negative Bar Colors in a Chart

Discussion in 'Microsoft Excel Charting' started by Guest, Oct 11, 2007.

  1. Guest

    Guest 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, Oct 11, 2007
    #1
    1. Advertisements

  2. Guest

    Guest 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.

    --
    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
     
    Guest, Oct 11, 2007
    #2
    1. Advertisements

  3. Guest

    Guest 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
     
    Guest, Oct 15, 2007
    #3
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Guest

    Changing Colors on an Excel Bar Chart

    Guest, Jun 24, 2004, in forum: Microsoft Excel Charting
    Replies:
    1
    Views:
    240
    Jon Peltier
    Jun 24, 2004
  2. Guest
    Replies:
    1
    Views:
    238
    Andy Pope
    Aug 26, 2005
  3. Guest
    Replies:
    2
    Views:
    1,532
    Guest
    Jun 19, 2007
  4. Breck
    Replies:
    9
    Views:
    5,404
    Jon Peltier
    Mar 4, 2008
  5. RPG

    Bar Chart to line chart excel 2007

    RPG, Aug 1, 2008, in forum: Microsoft Excel Charting
    Replies:
    2
    Views:
    810
    Del Cotter
    Aug 1, 2008
Loading...

Share This Page