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




<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


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


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


Here is the code...<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


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


<o


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


<o


Thanks,<o


<o


Dan<o

