5

I am trying to have the points in a chart change color if they are within certain value paramaters (i.e., >1 is green, <1 is red, anything else is blue). I cannot determine how to get VBA to give me the value of any given point.

In this thread, previously answered, the answer (very helpful in other ways) indicates that points(num).value will return the value at the point. However, I am getting an error message doing this, and nowhere else online or in the VBA help can I find a method that corresponds to this. Has anyone else had any success with this?

Here's the snippet of code giving me trouble: 

For Count = 1 To 7
    If Worksheets("Sheet1").ChartObjects("ChartName").Chart.SeriesCollection(1).Points(Count).Value > 1 Then
    '... do stuff

Because of the way the data are stored in the dataset, it would definitely be better to get the value from the chart directly. I could figure out a workaround using the dataset itself, but I would rather avoid that.

Community
  • 1
  • 1
Joe
  • 53
  • 1
  • 1
  • 3
  • What error message are you getting? Could you post a more complete version of your code? Also, I removed your apology; it's actually Stack Overflow rules that if you have a new question, you should post a new question instead of asking in an existing one, so good on you for that. – LittleBobbyTables - Au Revoir Feb 27 '13 at 21:04
  • It says "Run-Time Error 438: Object doesn't support this property or method". Seems pretty straightforward... i was mostly wondering if the same property existed, simply with a different name. There wasn't much more to the code, since I figured out that this was for sure the part that was giving me the error. I didn't get around to actually implementing the "do stuff" yet, but it wouldn't even open, for example, a "msgbox" confirming it had passed the line. And by the way, in this particular instance the value of the point was indeed greater than 1!! – Joe Feb 27 '13 at 21:39

1 Answers1

13
Sub Tester()

Dim cht As Chart, s As Series, p As Point
Dim vals, x As Integer

    Set cht = ActiveSheet.ChartObjects(1).Chart
    Set s = cht.SeriesCollection(1)

    vals = s.Values

    For x = LBound(vals) To UBound(vals)
      If vals(x) > 10 Then
        With s.Points(x)
            .MarkerBackgroundColor = RGB(255, 0, 0)
            .MarkerForegroundColor = RGB(255, 0, 0)
        End With
      End If
    Next x

End Sub
Tim Williams
  • 154,628
  • 8
  • 97
  • 125
  • This is amazing, thank you so much! I've adapted it to what I need it to do (read: complicated and dirtied it up); what I can't understand now is why it is doing what I need on all but one of my charts. I've posted my code at http://pastebin.com/yTqEnw01 if you want to take a look. Basically, it's color coding every chart correctly, but chart "2" is always green; the colors never change. – Joe Feb 28 '13 at 16:12
  • Hard to say without seeing the whole workbook: didn't see any obvious issues with your code. – Tim Williams Feb 28 '13 at 17:13
  • I think it ended up being a problem with Excel not recognizing the chart title. That is, I named the charts "1", "2", "3", etc., so that I could iterate through them, but VBA was acting as if I was talking about chart(1) as opposed to chart("1"). I added a "case select" to spit out an actual chart name and it fixed the problem; also made the code more legible to people more novice than me. Thanks for your help; I would upvote but my reputation score is not high enough yet! – Joe Feb 28 '13 at 18:02
  • No problem: please "accept" the answer though (by clicking on the checkmark). This helps anyone who comes along later with a similar question to find an answer. – Tim Williams Feb 28 '13 at 18:34
  • 3
    It's a shame `Point.Value` does not exist. This approach is just as good once you get it written out. Used the technique here to [delete data labels based on value](http://superuser.com/questions/920162/hide-data-label-containing-series-name-if-value-is-zero/920246#920246). – Byron Wall May 27 '15 at 15:02