Data Point values from chart

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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?
 
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.
 
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.
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Conflict 8
Format Data Series in a Pivot Chart 2
How to change the row number in macro code? 1
Run macro on refresh data (pivot table) 0
macro for pivot table 1
Help with events 6
keep getting the error message - Compile error: Else without If 2
piece of code keeps failing.... 2

Back
Top