Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Star Lounger
    Join Date
    Nov 2001
    Location
    Texas, USA
    Posts
    93
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Different colored data points in a graph? (Excel '97 or 2000)

    Hi, all. I am wondering if there is a way to make the data points in graphs change color according to their value. I'm trying to create a 'dashboard' that shows red when the data is below an assigned value, black if equal, or green if above the value. Is there any way to get a graph to do this?
    Thanks for any and all help! Judy

  2. #2
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Different colored data points in a graph? (Excel '97 or 2000)

    I know of no way to have the chart change colour - just the data it is based on.
    Perhaps a VBA method will make this possible. I'll watch this thread with interest!

    Cheers
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  3. #3
    New Lounger
    Join Date
    Sep 2001
    Location
    Paignton, Devon, England
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Different colored data points in a graph? (Excel '97 or 2000)

    I use this to change the colour of out-of-limits points on control charts in excel:

    Private Sub MarkPoint(ByVal i As Long, j As Long)
    ''''change the marker style of points outside control limits
    Dim srs As Series
    Dim pnt As Point

    On Error Resume Next
    Set srs = myChart.SeriesCollection(1)
    With srs
    Set pnt = .Points(i)
    pnt.MarkerBackgroundColorIndex = j
    pnt.MarkerForegroundColorIndex = j
    pnt.MarkerSize = 7
    pnt.MarkerStyle = xlMarkerStyleCircle
    End With
    On Error GoTo 0

    End Sub

    A bit of hacking about should get it to work for you - hope it helps

    Graeme

  4. #4
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Different colored data points in a graph? (Excel '97 or 2000)

    That was a fun problem, but I don't like the solution very much. As the macro below shows, I moved the chart values to an array v. I could access them directly via "for each y in .Values", but could not access them via .Values(i) nor .Values.Item(i). What's up with this? Anyway, I shouldn't knock sucess. I've attached the workbook, but here's the macro:<pre>Option Explicit
    Sub ColorColumns()
    Const BAD = 12000, OK = 15000
    Const RED = 3, BLACK = 1, GREEN = 50
    Dim i As Integer
    Dim v() As Variant
    If TypeName(ActiveSheet) <> "Chart" Then Exit Sub
    With ActiveChart.SeriesCollection(1)
    v = .Values
    For i = 1 To .Points.Count
    Select Case v(i)
    Case Is > OK
    .Points(i).Interior.ColorIndex = GREEN
    Case Is > BAD
    .Points(i).Interior.ColorIndex = BLACK
    Case Else
    .Points(i).Interior.ColorIndex = RED
    End Select
    Next i
    End With
    End Sub</pre>

    Attached Files Attached Files
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  5. #5
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Different colored data points in a graph? (Excel '97 or 2000)

    Judy,

    I had a somewhat related problem last summer in a thread about Coloring the Bars . In this problem, I wanted to be able to make the bars of a bar chart a particular color depending on the grade (student test scores in range1 had color1, in range2 had color2, etc.). I know your post talked about wanting to color the data points, but maybe the approach in the above can help.

    Fred

  6. #6
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Different colored data points in a graph? (Excel '97 or 2000)

    Sammy,

    I downloaded your spreadsheet and tried running it in Excel 97 SR1. Got a compile error "can't assign to array" for the statement
    v = .Values

    Since this was something I was interested in (see my post on this thread for a somewhat related solution), I'd thought I'd give it a whirl. Any ideas on this?

    Fred

  7. #7
    Star Lounger
    Join Date
    Nov 2001
    Location
    Texas, USA
    Posts
    93
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Different colored data points in a graph? (Excel '97 or 2000)

    Sammy,
    Thanks a bunch! I see the colored bars, now I need to look at the macro and make sense of it. I'll also play with it to see if data points can also be changed this way.
    Much appreciated- I wish I had your knowledge. Thank heavens for this forum.
    Judy

  8. #8
    Star Lounger
    Join Date
    Nov 2001
    Location
    Texas, USA
    Posts
    93
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Different colored data points in a graph? (Excel '97 or 2000)

    Grame,
    hanks so much. Control charts definitely are part of what I want. I'll play with this.
    Sam, the next responder, attached an excel file which goes about it a different way. You might be interested in that also.
    Judy

  9. #9
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Different colored data points in a graph? (Excel '97 or 2000)

    Judy, sorry, I just assumed that you had a bar chart. For a scatter chart, you want to mess with MarkerBackgroundColorIndex:<pre>Sub ColorPoints()
    Const BAD = 12000, OK = 15000
    Const RED = 3, BLACK = 1, GREEN = 50
    Dim i As Integer
    Dim v() As Variant
    If TypeName(ActiveSheet) <> "Chart" Then Exit Sub
    With ActiveChart.SeriesCollection(1)
    v = .Values
    For i = 1 To .Points.Count
    Select Case v(i)
    Case Is > OK
    .Points(i).MarkerBackgroundColorIndex = GREEN
    .Points(i).MarkerForegroundColorIndex = GREEN
    .Points(i).MarkerSize = 10
    Case Is > BAD
    .Points(i).MarkerBackgroundColorIndex = BLACK
    .Points(i).MarkerForegroundColorIndex = BLACK
    .Points(i).MarkerSize = 10
    Case Else
    .Points(i).MarkerBackgroundColorIndex = RED
    .Points(i).MarkerForegroundColorIndex = RED
    .Points(i).MarkerSize = 10
    End Select
    Next i
    End With
    End Sub</pre>

    Notice that I also made the points bigger, so that they could be seen. What type of chart are you using? Are there lines that need to be colored, also?
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  10. #10
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Different colored data points in a graph? (Excel '97 or 2000)

    RE: Fred's post on using XL 97, can't assign to array

    Fred, I guess you could use a for-each loop on .Values and move them one at a time to v, but that is really ugly. Somebody needs to figure out how to access .Values directly. I'll work on it, but not today. --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  11. #11
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Different colored data points in a graph? (Excel '97 or 2000)

    Sammy,

    On a PC with Excel 2000, I tried taking the
    v = .Values
    statement from your code and putting it into a loop. But I couldn't get the thing to work no matter what combination of things I had on the right side. Of course, I was just guessing the syntax [v(j)=...(j)] last night when I had no references. Now I'm looking at Walkenbach's 2000 Power Pgmg with VBA. It says (pg 490): the Values property returns a variant array. So what you had should have worked.

    Your different colors for the bars (as opposed to the scatter chart) helped me at least to the extent that this was possible to do (or it's almost there). If you look at the thread I referred to earlier in this thread, you'll see I originally thought there was a VBA solution to my problem. However between Hans and myself, we came up with several non VBA solutions. My problem was that a teacher wanted a bar chart where each student was on the x-axis but the height of the bar was dependent on the grade. So, for example, below 70 was failing and that was red; 70-79=blue,80-89=orange;99+=green.

    Fred

  12. #12
    Star Lounger
    Join Date
    Nov 2001
    Location
    Texas, USA
    Posts
    93
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Different colored data points in a graph? (Excel '97 or 2000)

    You are incredible! I'm really trying to make a dashboard for the executives, the kind that has 10-12 specific
    numeric targets and the associated ongoing (quarterly) data that tells them without looking whether we are
    exceeding or way behind target. So most of them will be simple line graphs with the data points changing
    color. I think we'd be better off not trying to change the lines since there is always one more data point than
    line, and they would invariably argue whether it should be the line before, or after, the data point.

    How/ where did you learn this? I'm just a simple Excel user struggling now with the conversion issues of '97 to
    2000. I have heard that Excel can create reports basically equal to what Access can do, and can hold macros
    and formulas which do all that I want. But I'm not a programmer, and have struggled to learn the simple SQL
    formulas needed to make Access useful.
    Judy

  13. #13
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Different colored data points in a graph? (Excel '97 or 2000)

    <P ID="edit" class=small>Edited by rory on 16-Jan-02 13:38.</P>Sam,
    As best I can figure (not saying much), the .values property seems to return a collection rather than an array, which is why you can use "For each y in .values ". I can only assume that no item method was implemented for the collection otherwise you ought to be able to use an index to retrieve an individual value.

    **Edited by me as my original post was rubbish! -Rory**
    Regards,
    Rory

    Microsoft MVP - Excel

  14. #14
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Different colored data points in a graph? (Excel '97 or 2000)

    ...we came up with several non VBA solutions...

    I'd be curious to know what they are.

    Cheers
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  15. #15
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Different colored data points in a graph? (Excel '97 or 2000)

    Catherine,

    Earlier in this thread, I posted the link to the thread from the summer. It was post number 107801 on 14-Jan-02 11:21. The link, per that email, is <A target="_blank" HREF=http://www.wopr.com/cgi-bin/w3t/showthreaded.pl?Cat=&Board=xl&Number=64133&Search= true&Forum=xl&Words=bar> coloring the bars </A>.

    There were 2 solutions - which were for bar graphs (which this thread seemed to start with). 1 came from Hans Pottel which used a pivot table chart. This is only available in Excel 2000. I came up with the other solution but don't recall what it was, altho I think it applied to Excel 97 as well. Both were discussed in the above linked thread; I think workbook solutions were attached there.

    I think I have a workbook for each of Hans' and my solutions. But that's on another computer. If you can't get what you want from the other thread, let me know and I'll attach each workbook to separate emails.

    Fred

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •