Changing Chart Bar/Column color based on negative values

Dan DeHaven · Sep 3, 2009

After realizing that the ability to control a Bar Charts color by way of the "Invert if Negative" setting to something other than white is no longer available in Excel 2007 I decided to write a code to handle the need.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com

ffice

ffice" /><o

></o

>
<o

></o

>
FYI, for those that don't know, you can't access the "Fill Effects" settings under the "Format Data Series" dialog box in Excel 2007. So now there is no way to set the "Color 1:" and "Color 2:" settings, which allowed you to essentially set the "Invert" color for negative values. Seriously frustrating!!! <o

></o

>
<o

></o

>
Often I like to use bar charts to display performance to prior periods and It's especially nice to have the positive series bars a color like green and the negative series bars red.<o

></o

>
<o

></o

>
I've come to an issue and I'm receiving the dreaded run-time error 13 (type mismatch) and I need some guidance. It's the "If" line that is highlighted in debug mode and apparently causing the error.<o

></o

>
<o

></o

>
Here is the code...<o

></o

>
<o

></o

>

Code:

[COLOR=black][FONT=Verdana]Sub InvertToRed()[/FONT][/COLOR]
[FONT=Verdana][COLOR=black]   '   Written by Dan DeHaven on 9/3/2009[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]   '   Use this code to change the ActiveCharts Bar or Column color to red if the value of[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]   '   the Series is negative[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]   '[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]   Dim s As Series[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]   '[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]   With ActiveChart[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]       For Each s In .SeriesCollection[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]           If s.Values < 0 Then s.Interior.Color = RGB(255, 0, 0)[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]       Next s[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]   End With[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]   '[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]End Sub<o:p></o:p>[/COLOR][/FONT]

<o

></o

>
<o

></o

>
Can anyone tell me what I've done wrong.<o

></o

>
<o

></o

>
Or I'm always open to suggestions for better code.<o

></o

>
<o

></o

>
Thanks,<o

></o

>
<o

></o

>
Dan<o

></o

>

Dan DeHaven · Sep 4, 2009

Only took a few painful hours but I was able to get the code to work with a few changes. I'm attaching the revised code below:

Code:

Sub SetNegativeChartPointColor()
    '   Written by Dan DeHaven on 9/3/2009
    '   Code changes any charts bar or column color to red if it's value is negative
    '
    Dim s As Series
    Dim v As Variant
    Dim i As Integer
    '
    With ActiveChart
       For Each s In .SeriesCollection
        i = 0 ' set/reset point counter
          For Each v In s.Values
           i = i + 1 ' set/increment point counter
            If v < 0 Then s.Points(i).Interior.Color = RGB(255, 0, 0)
          Next v
       Next s
   End With
End Sub

It can quickly be changed to work on all charts on a worksheet or in a workbook. Also, you could add additional "If" lines (or a Select statement) for multiple value criteria and easily change the RGB color to suite your needs.

Please let me know if can think of any easier or better ways of getting this result.

Dan DeHaven

Andy Pope · Sep 4, 2009

For a non VBA solution you can use an additional series and some formula.

See Jon's page on conditional charts
http://peltiertech.com/Excel/Charts/ConditionalChart1.html

pgc01 · Sep 4, 2009

Hi Dan

For a vba solution I'd use your method, just with less variables:

Code:

Sub SetNegativeChartPointColor()
    '   Written by Dan DeHaven on 9/3/2009
    '   Code changes any charts bar or column color to red if it's value is negative
    '
    Dim s As Series
    Dim i As Long
    
    With ActiveChart
        For Each s In .SeriesCollection
            For i = 1 To s.Points.Count
                If s.Values(i) < 0 Then s.Points(i).Interior.Color = RGB(255, 0, 0)
            Next i
        Next s
   End With
End Sub

Remark: this code assumes that all series will have the same colour for negative values.

Dan DeHaven · Sep 4, 2009

Andy, Thanks for the link. I did look through several of peltiertech tips yesterday but I don't think I read the one you posted. He does have many good ideas well documented, definitely worth a look.

pgc01, I appreciate you taking a look at the code. I had a hunch I could drop that variable and now that I really look at it I'm not sure why I put it in there. It was late....

Thanks much!

Dan

Changing Chart Bar/Column color based on negative values

Dan DeHaven

New Member

Dan DeHaven

New Member

Andy Pope

Active Member

pgc01

MrExcel MVP

Dan DeHaven

New Member

Similar threads

Share this page

Changing Chart Bar/Column color based on negative values

Dan DeHaven

New Member

Dan DeHaven

New Member

Andy Pope

Active Member

pgc01

MrExcel MVP

Dan DeHaven

New Member

Similar threads

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock