Changing Chart Bar/Column color based on negative values

Dan DeHaven

New Member
Joined
Aug 26, 2009
Messages
45
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:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p></o:p>
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:p></o:p>
<o:p></o:p>
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:p></o:p>
<o:p></o:p>
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:p></o:p>
<o:p></o:p>
Here is the code...<o:p></o:p>
<o:p></o:p>
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:p></o:p>
<o:p></o:p>
Can anyone tell me what I've done wrong.<o:p></o:p>
<o:p></o:p>
Or I'm always open to suggestions for better code.<o:p></o:p>
<o:p></o:p>
Thanks,<o:p></o:p>
<o:p></o:p>
Dan<o:p></o:p>
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
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
 
Last edited:
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,212,934
Messages
6,110,762
Members
448,295
Latest member
Uzair Tahir Khan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top