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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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?