Okay, don't know if it's do-able but I'd like to have all my series with a
negative value be red and all positive be green.
I tried something like this
For i = 1 to SeriesNum
SerVal = ActiveChart.Series(i).value
If SerVal <0 then
With Selection.Interior
.ColorIndex = 3
.Pattern = xlSolid
End With
Else
With Selection.Interior
.ColorIndex = 3
.Pattern = xlSolid
End With
End if
Next i

It doesn't work. Am I going about this the wrong way?

this worked fine with a column chart with multiple series:

Sub Tester1()
Dim ser As Series
Dim pt As Point
Dim SeriesNum As Long
Dim i As Long, j As Long
Dim vX As Variant, vY As Variant
SeriesNum = ActiveChart.SeriesCollection.Count
For i = 1 To SeriesNum
Set ser = ActiveChart.SeriesCollection(i)
vX = ser.XValues
vY = ser.Values
j = 0
For Each pt In ser.Points
j = j + 1
If vY(j) < 0 Then
With pt.Interior
.ColorIndex = 3
.Pattern = xlSolid
End With
Else
With pt.Interior
.ColorIndex = 4
.Pattern = xlSolid
End With
End If
Next pt
Next i

End Sub

if your chart is a line chart or other type chart, you would have to use the
properties appropriate to the markers on that chart.

--
Regards,
Tom Ogilvy


"RominallL" wrote:

> Okay, don't know if it's do-able but I'd like to have all my series with a
> negative value be red and all positive be green.
> I tried something like this
> For i = 1 to SeriesNum
> SerVal = ActiveChart.Series(i).value
> If SerVal <0 then
> With Selection.Interior
> .ColorIndex = 3
> .Pattern = xlSolid
> End With
> Else
> With Selection.Interior
> .ColorIndex = 3
> .Pattern = xlSolid
> End With
> End if
> Next i
>
> It doesn't work. Am I going about this the wrong way?

That was GREAT but it didn't update the legend. Do I need to do something to
fix that or just add the legend after I've finished coloring the bars?

"Tom Ogilvy" wrote:

> this worked fine with a column chart with multiple series:
>
> Sub Tester1()
> Dim ser As Series
> Dim pt As Point
> Dim SeriesNum As Long
> Dim i As Long, j As Long
> Dim vX As Variant, vY As Variant
> SeriesNum = ActiveChart.SeriesCollection.Count
> For i = 1 To SeriesNum
> Set ser = ActiveChart.SeriesCollection(i)
> vX = ser.XValues
> vY = ser.Values
> j = 0
> For Each pt In ser.Points
> j = j + 1
> If vY(j) < 0 Then
> With pt.Interior
> .ColorIndex = 3
> .Pattern = xlSolid
> End With
> Else
> With pt.Interior
> .ColorIndex = 4
> .Pattern = xlSolid
> End With
> End If
> Next pt
> Next i
>
> End Sub
>
> if your chart is a line chart or other type chart, you would have to use the
> properties appropriate to the markers on that chart.
>
> --
> Regards,
> Tom Ogilvy
>
>
> "RominallL" wrote:
>
> > Okay, don't know if it's do-able but I'd like to have all my series with a
> > negative value be red and all positive be green.
> > I tried something like this
> > For i = 1 to SeriesNum
> > SerVal = ActiveChart.Series(i).value
> > If SerVal <0 then
> > With Selection.Interior
> > .ColorIndex = 3
> > .Pattern = xlSolid
> > End With
> > Else
> > With Selection.Interior
> > .ColorIndex = 3
> > .Pattern = xlSolid
> > End With
> > End if
> > Next i
> >
> > It doesn't work. Am I going about this the wrong way?

At least for the column chart, you are coloring the bars, but not the series
- so the legend isn't updated - but it isn't obvious what you want the legend
to show. I had 4 series and for each series, about half the bars were red
and the other half green - so unless there were few enough bars so the
grouping was obvious, it was difficult to tell which bar belonged to which
series.

The legend is designed to have mono colored series with each series either
unique in color or othewise discernable (such as in line charts, the markers
can be different shapes).

Perhaps you could explain what you have and what you want the legent to
portray.

--
Regards,
Tom Ogilvy


"RominallL" wrote:

> That was GREAT but it didn't update the legend. Do I need to do something to
> fix that or just add the legend after I've finished coloring the bars?
>
> "Tom Ogilvy" wrote:
>
> > this worked fine with a column chart with multiple series:
> >
> > Sub Tester1()
> > Dim ser As Series
> > Dim pt As Point
> > Dim SeriesNum As Long
> > Dim i As Long, j As Long
> > Dim vX As Variant, vY As Variant
> > SeriesNum = ActiveChart.SeriesCollection.Count
> > For i = 1 To SeriesNum
> > Set ser = ActiveChart.SeriesCollection(i)
> > vX = ser.XValues
> > vY = ser.Values
> > j = 0
> > For Each pt In ser.Points
> > j = j + 1
> > If vY(j) < 0 Then
> > With pt.Interior
> > .ColorIndex = 3
> > .Pattern = xlSolid
> > End With
> > Else
> > With pt.Interior
> > .ColorIndex = 4
> > .Pattern = xlSolid
> > End With
> > End If
> > Next pt
> > Next i
> >
> > End Sub
> >
> > if your chart is a line chart or other type chart, you would have to use the
> > properties appropriate to the markers on that chart.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "RominallL" wrote:
> >
> > > Okay, don't know if it's do-able but I'd like to have all my series with a
> > > negative value be red and all positive be green.
> > > I tried something like this
> > > For i = 1 to SeriesNum
> > > SerVal = ActiveChart.Series(i).value
> > > If SerVal <0 then
> > > With Selection.Interior
> > > .ColorIndex = 3
> > > .Pattern = xlSolid
> > > End With
> > > Else
> > > With Selection.Interior
> > > .ColorIndex = 3
> > > .Pattern = xlSolid
> > > End With
> > > End if
> > > Next i
> > >
> > > It doesn't work. Am I going about this the wrong way?

An alternative, non VBA approach, which allows different legends for
positive and negative formats:

http://peltiertech.com/Excel/Charts/...nalChart1.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"RominallL" <(E-Mail Removed)> wrote in message
news:CD40F899-2EE3-44EA-90FE-(E-Mail Removed)...
> Okay, don't know if it's do-able but I'd like to have all my series with
> a
> negative value be red and all positive be green.
> I tried something like this
> For i = 1 to SeriesNum
> SerVal = ActiveChart.Series(i).value
> If SerVal <0 then
> With Selection.Interior
> .ColorIndex = 3
> .Pattern = xlSolid
> End With
> Else
> With Selection.Interior
> .ColorIndex = 3
> .Pattern = xlSolid
> End With
> End if
> Next i
>
> It doesn't work. Am I going about this the wrong way?

Well, I am using bars The range of series entries is from 30-75 so knowing
that the 10th green bar is for John Doe makes matching easier. I'm begining
to feel that this is not do-able with excel VB.


"Tom Ogilvy" wrote:

> At least for the column chart, you are coloring the bars, but not the series
> - so the legend isn't updated - but it isn't obvious what you want the legend
> to show. I had 4 series and for each series, about half the bars were red
> and the other half green - so unless there were few enough bars so the
> grouping was obvious, it was difficult to tell which bar belonged to which
> series.
>
> The legend is designed to have mono colored series with each series either
> unique in color or othewise discernable (such as in line charts, the markers
> can be different shapes).
>
> Perhaps you could explain what you have and what you want the legent to
> portray.
>
> --
> Regards,
> Tom Ogilvy
>
>
> "RominallL" wrote:
>
> > That was GREAT but it didn't update the legend. Do I need to do something to
> > fix that or just add the legend after I've finished coloring the bars?
> >
> > "Tom Ogilvy" wrote:
> >
> > > this worked fine with a column chart with multiple series:
> > >
> > > Sub Tester1()
> > > Dim ser As Series
> > > Dim pt As Point
> > > Dim SeriesNum As Long
> > > Dim i As Long, j As Long
> > > Dim vX As Variant, vY As Variant
> > > SeriesNum = ActiveChart.SeriesCollection.Count
> > > For i = 1 To SeriesNum
> > > Set ser = ActiveChart.SeriesCollection(i)
> > > vX = ser.XValues
> > > vY = ser.Values
> > > j = 0
> > > For Each pt In ser.Points
> > > j = j + 1
> > > If vY(j) < 0 Then
> > > With pt.Interior
> > > .ColorIndex = 3
> > > .Pattern = xlSolid
> > > End With
> > > Else
> > > With pt.Interior
> > > .ColorIndex = 4
> > > .Pattern = xlSolid
> > > End With
> > > End If
> > > Next pt
> > > Next i
> > >
> > > End Sub
> > >
> > > if your chart is a line chart or other type chart, you would have to use the
> > > properties appropriate to the markers on that chart.
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > >
> > > "RominallL" wrote:
> > >
> > > > Okay, don't know if it's do-able but I'd like to have all my series with a
> > > > negative value be red and all positive be green.
> > > > I tried something like this
> > > > For i = 1 to SeriesNum
> > > > SerVal = ActiveChart.Series(i).value
> > > > If SerVal <0 then
> > > > With Selection.Interior
> > > > .ColorIndex = 3
> > > > .Pattern = xlSolid
> > > > End With
> > > > Else
> > > > With Selection.Interior
> > > > .ColorIndex = 3
> > > > .Pattern = xlSolid
> > > > End With
> > > > End if
> > > > Next i
> > > >
> > > > It doesn't work. Am I going about this the wrong way?

Didn't you read my reply to your post?

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"RominallL" <(E-Mail Removed)> wrote in message
news:4CA17F3C-B3E1-4463-B58B-(E-Mail Removed)...
> Well, I am using bars The range of series entries is from 30-75 so
> knowing
> that the 10th green bar is for John Doe makes matching easier. I'm
> begining
> to feel that this is not do-able with excel VB.
>
>
> "Tom Ogilvy" wrote:
>
>> At least for the column chart, you are coloring the bars, but not the
>> series
>> - so the legend isn't updated - but it isn't obvious what you want the
>> legend
>> to show. I had 4 series and for each series, about half the bars were
>> red
>> and the other half green - so unless there were few enough bars so the
>> grouping was obvious, it was difficult to tell which bar belonged to
>> which
>> series.
>>
>> The legend is designed to have mono colored series with each series
>> either
>> unique in color or othewise discernable (such as in line charts, the
>> markers
>> can be different shapes).
>>
>> Perhaps you could explain what you have and what you want the legent to
>> portray.
>>
>> --
>> Regards,
>> Tom Ogilvy
>>
>>
>> "RominallL" wrote:
>>
>> > That was GREAT but it didn't update the legend. Do I need to do
>> > something to
>> > fix that or just add the legend after I've finished coloring the bars?
>> >
>> > "Tom Ogilvy" wrote:
>> >
>> > > this worked fine with a column chart with multiple series:
>> > >
>> > > Sub Tester1()
>> > > Dim ser As Series
>> > > Dim pt As Point
>> > > Dim SeriesNum As Long
>> > > Dim i As Long, j As Long
>> > > Dim vX As Variant, vY As Variant
>> > > SeriesNum = ActiveChart.SeriesCollection.Count
>> > > For i = 1 To SeriesNum
>> > > Set ser = ActiveChart.SeriesCollection(i)
>> > > vX = ser.XValues
>> > > vY = ser.Values
>> > > j = 0
>> > > For Each pt In ser.Points
>> > > j = j + 1
>> > > If vY(j) < 0 Then
>> > > With pt.Interior
>> > > .ColorIndex = 3
>> > > .Pattern = xlSolid
>> > > End With
>> > > Else
>> > > With pt.Interior
>> > > .ColorIndex = 4
>> > > .Pattern = xlSolid
>> > > End With
>> > > End If
>> > > Next pt
>> > > Next i
>> > >
>> > > End Sub
>> > >
>> > > if your chart is a line chart or other type chart, you would have to
>> > > use the
>> > > properties appropriate to the markers on that chart.
>> > >
>> > > --
>> > > Regards,
>> > > Tom Ogilvy
>> > >
>> > >
>> > > "RominallL" wrote:
>> > >
>> > > > Okay, don't know if it's do-able but I'd like to have all my
>> > > > series with a
>> > > > negative value be red and all positive be green.
>> > > > I tried something like this
>> > > > For i = 1 to SeriesNum
>> > > > SerVal = ActiveChart.Series(i).value
>> > > > If SerVal <0 then
>> > > > With Selection.Interior
>> > > > .ColorIndex = 3
>> > > > .Pattern = xlSolid
>> > > > End With
>> > > > Else
>> > > > With Selection.Interior
>> > > > .ColorIndex = 3
>> > > > .Pattern = xlSolid
>> > > > End With
>> > > > End if
>> > > > Next i
>> > > >
>> > > > It doesn't work. Am I going about this the wrong way?

That worked great. Thanks

"Jon Peltier" wrote:

> An alternative, non VBA approach, which allows different legends for
> positive and negative formats:
>
> http://peltiertech.com/Excel/Charts/...nalChart1.html
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Tutorials and Custom Solutions
> http://PeltierTech.com
> _______
>
>
> "RominallL" <(E-Mail Removed)> wrote in message
> news:CD40F899-2EE3-44EA-90FE-(E-Mail Removed)...
> > Okay, don't know if it's do-able but I'd like to have all my series with
> > a
> > negative value be red and all positive be green.
> > I tried something like this
> > For i = 1 to SeriesNum
> > SerVal = ActiveChart.Series(i).value
> > If SerVal <0 then
> > With Selection.Interior
> > .ColorIndex = 3
> > .Pattern = xlSolid
> > End With
> > Else
> > With Selection.Interior
> > .ColorIndex = 3
> > .Pattern = xlSolid
> > End With
> > End if
> > Next i
> >
> > It doesn't work. Am I going about this the wrong way?
>
>
>

Yes and it was great but not until I tried the first response.

"Jon Peltier" wrote:

> Didn't you read my reply to your post?
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Tutorials and Custom Solutions
> http://PeltierTech.com
> _______
>
>
> "RominallL" <(E-Mail Removed)> wrote in message
> news:4CA17F3C-B3E1-4463-B58B-(E-Mail Removed)...
> > Well, I am using bars The range of series entries is from 30-75 so
> > knowing
> > that the 10th green bar is for John Doe makes matching easier. I'm
> > begining
> > to feel that this is not do-able with excel VB.
> >
> >
> > "Tom Ogilvy" wrote:
> >
> >> At least for the column chart, you are coloring the bars, but not the
> >> series
> >> - so the legend isn't updated - but it isn't obvious what you want the
> >> legend
> >> to show. I had 4 series and for each series, about half the bars were
> >> red
> >> and the other half green - so unless there were few enough bars so the
> >> grouping was obvious, it was difficult to tell which bar belonged to
> >> which
> >> series.
> >>
> >> The legend is designed to have mono colored series with each series
> >> either
> >> unique in color or othewise discernable (such as in line charts, the
> >> markers
> >> can be different shapes).
> >>
> >> Perhaps you could explain what you have and what you want the legent to
> >> portray.
> >>
> >> --
> >> Regards,
> >> Tom Ogilvy
> >>
> >>
> >> "RominallL" wrote:
> >>
> >> > That was GREAT but it didn't update the legend. Do I need to do
> >> > something to
> >> > fix that or just add the legend after I've finished coloring the bars?
> >> >
> >> > "Tom Ogilvy" wrote:
> >> >
> >> > > this worked fine with a column chart with multiple series:
> >> > >
> >> > > Sub Tester1()
> >> > > Dim ser As Series
> >> > > Dim pt As Point
> >> > > Dim SeriesNum As Long
> >> > > Dim i As Long, j As Long
> >> > > Dim vX As Variant, vY As Variant
> >> > > SeriesNum = ActiveChart.SeriesCollection.Count
> >> > > For i = 1 To SeriesNum
> >> > > Set ser = ActiveChart.SeriesCollection(i)
> >> > > vX = ser.XValues
> >> > > vY = ser.Values
> >> > > j = 0
> >> > > For Each pt In ser.Points
> >> > > j = j + 1
> >> > > If vY(j) < 0 Then
> >> > > With pt.Interior
> >> > > .ColorIndex = 3
> >> > > .Pattern = xlSolid
> >> > > End With
> >> > > Else
> >> > > With pt.Interior
> >> > > .ColorIndex = 4
> >> > > .Pattern = xlSolid
> >> > > End With
> >> > > End If
> >> > > Next pt
> >> > > Next i
> >> > >
> >> > > End Sub
> >> > >
> >> > > if your chart is a line chart or other type chart, you would have to
> >> > > use the
> >> > > properties appropriate to the markers on that chart.
> >> > >
> >> > > --
> >> > > Regards,
> >> > > Tom Ogilvy
> >> > >
> >> > >
> >> > > "RominallL" wrote:
> >> > >
> >> > > > Okay, don't know if it's do-able but I'd like to have all my
> >> > > > series with a
> >> > > > negative value be red and all positive be green.
> >> > > > I tried something like this
> >> > > > For i = 1 to SeriesNum
> >> > > > SerVal = ActiveChart.Series(i).value
> >> > > > If SerVal <0 then
> >> > > > With Selection.Interior
> >> > > > .ColorIndex = 3
> >> > > > .Pattern = xlSolid
> >> > > > End With
> >> > > > Else
> >> > > > With Selection.Interior
> >> > > > .ColorIndex = 3
> >> > > > .Pattern = xlSolid
> >> > > > End With
> >> > > > End if
> >> > > > Next i
> >> > > >
> >> > > > It doesn't work. Am I going about this the wrong way?
>
>
>

Glad it worked.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"RominallL" <(E-Mail Removed)> wrote in message
news:EA962C90-1EF2-40EB-A06C-(E-Mail Removed)...
> That worked great. Thanks
>
> "Jon Peltier" wrote:
>
>> An alternative, non VBA approach, which allows different legends for
>> positive and negative formats:
>>
>> http://peltiertech.com/Excel/Charts/...nalChart1.html
>>
>> - Jon
>> -------
>> Jon Peltier, Microsoft Excel MVP
>> Tutorials and Custom Solutions
>> http://PeltierTech.com
>> _______
>>
>>
>> "RominallL" <(E-Mail Removed)> wrote in message
>> news:CD40F899-2EE3-44EA-90FE-(E-Mail Removed)...
>> > Okay, don't know if it's do-able but I'd like to have all my series
>> > with
>> > a
>> > negative value be red and all positive be green.
>> > I tried something like this
>> > For i = 1 to SeriesNum
>> > SerVal = ActiveChart.Series(i).value
>> > If SerVal <0 then
>> > With Selection.Interior
>> > .ColorIndex = 3
>> > .Pattern = xlSolid
>> > End With
>> > Else
>> > With Selection.Interior
>> > .ColorIndex = 3
>> > .Pattern = xlSolid
>> > End With
>> > End if
>> > Next i
>> >
>> > It doesn't work. Am I going about this the wrong way?
>>
>>
>>