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

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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:

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

…we came up with several non VBA solutions…

I’d be curious to know what they are.

Cheers

Reply | Quote

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 coloring the bars .

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

Reply | Quote

Thank you for pointing to the previous thread.

Cheers

Reply | Quote

Edited by rory on 16-Jan-02 13:38.

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**

Reply | Quote

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

Reply | Quote

Judy, sorry, I just assumed that you had a bar chart. For a scatter chart, you want to mess with MarkerBackgroundColorIndex:

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

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?

Reply | Quote

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

Reply | Quote

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

Reply | Quote

Well, better late than never. There is a way to format the points/bars based on the values, don’t even need VBA. Check out John Peltier solution at http://www.geocities.com/jonpeltier/Excel/…onalChart1.html. The rest of his site also has some great tips. –Sam

Reply | Quote