View Single Post
  #6   Report Post  
Posted to microsoft.public.excel.programming
RominallL RominallL is offline
external usenet poster
 
Posts: 26
Default Data Point values from chart

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?