Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Point values from chart
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Point values from chart
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Point values from 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? "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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Point values from chart
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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Point values from chart
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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Point values from chart
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? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Point values from chart
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? |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Point values from chart
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? |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Point values from chart
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Moving a line chart data point revises data table value in Excel ' | Charts and Charting in Excel | |||
Read Chart Point Values When Hover in 2007 | Excel Discussion (Misc queries) | |||
Is there a property to get the values for a point in a chart series? | Excel Programming | |||
Indexing Values to Same Starting Point for Relative Return Chart | Charts and Charting in Excel | |||
How to remove a data point in a chart? | Charts and Charting in Excel |