Color code data points on a scatter chart (2007)

Hi Everyone,

Does anyone know if there's a way to color code points on a scatter chart based on their values in a "3rd dimension" (i.e. other than the 2 dimensions represented by the chart axes).

You might imagine a scatter chart that plots transactions by price (y) and units purchased (x).

In that scenario, it might also be nice to color code each point by the region that made the sale.

The only way I know to do this is to manually create separate columns of data for each region, so that each region can be plotted on the scatter chart as a separate data series.

But since I'd like to experiment with several color coding alternatives, I'm wondering if there isn't a simpler way...

For instance, Rob Bovey's "XY Chart Labeler" can add data point labels to a scatter chart...

Is there, perhaps, another add-in that might enable color coding of data points based on a 3rd set of values?

Just wonderin'...

Cheers,

Jay

In can use code to set the colour of each point based on a set of value bands.

Sub x()
    
    Dim objCht As ChartObject
    Dim rngData As Range
    Dim lngIndex As Long
    Dim lngColor As Long
    
    Set rngData = Range("C2:C6")
    Set objCht = ActiveSheet.ChartObjects(1)
    With objCht.Chart
        With .SeriesCollection(1)
            For lngIndex = 1 To .Points.Count
                Select Case rngData.Cells(lngIndex, 1).Value
                Case Is > 7
                    lngColor = RGB(0, 0, 255)
                Case Is > 4
                    lngColor = RGB(0, 255, 0)
                Case Else
                    lngColor = RGB(255, 0, 0)
                End Select
                With .Points(lngIndex).Format.Fill
                    .ForeColor.RGB = lngColor
                    .BackColor.RGB = lngColor
                End With
            Next
        End With
    End With
End Sub

In this case C2:C6 contains a value between 0 and 10 that has 1 of 3 colours assigned.

Yes you will need to run the macro again in order for it to colour based on the new data.

ALT+F8 will display the macro dialog, where you can select the macro and run it.

You can assign the macro to a shape or button to make it easier to refresh.

Originally Posted by JayUSA

Andy!


However, I find that when I change the values in "3rd Value" column, the colors of the scatter points don't change.

Cheers,

Jay

You can use it as a sub with worksheet change event. That will run this module whenever data on the worksheet changes. But note that when anything (means ANYTHING) changes on the worksheet, this module will execute.

Or a better way is to change this macro to change only when a particular range is updated, and this range would be the cells where your data lies (I have not seen the attached excel so could not specify which columns and which rows)

Bernadine,
Welcome to the Forum, unfortunately:

1. Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.

2. Please do not quote whole posts.