Color code data points on a scatter chart (2007)

**JayUSA** · 06-13-2009, 04:19 PM

Andy!

Again, thank you very much for your thoughtful and detailed response to my question!

I have downloaded your spreadsheet, and I am keen to use it as a model.

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

In other words, the color of the scatter points may have initially correlated with the values of "3rd Value", but they don't stay synced.

Do I need to explicitly run or refresh the VBA script somehow? I'm afraid, I'm not VBA literate so I really don't know.

Cheers,

Jay

**Andy Pope** · 06-14-2009, 06:31 AM

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.

**Excel Matic** · 06-14-2009, 12:24 PM

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)

**JayUSA** · 06-14-2009, 04:01 PM

Great Guys!

Now I get it! Very helpful & problem solved!

Thanks again, very much!

Jay

**JayUSA** · 06-14-2009, 09:28 PM

First of all.... a big THANK YOU to Andy and Mohit.

Second... I thought I was all set-to-go, but then I ran into lots of problems actually trying to apply this VBA code to my worksheet. The problems started as soon as converted the chart from an object to a sheet. Then I got even more confused...

So.. I am now attaching a very, very simple and very clean micro-version of my spreadsheet.

If one of you, or someone else, could get the scatter plots to receive a color according to their source ("source1", "source2",etc...), I would indeed be very, very grateful.

Since I have next to zero VBA skills, I think it will be much more instructive for me to see how this code is applied to my specific situation, than to try to blindly apply it myself.

Again, I could not be more grateful!

Cheers,

Jay

**Andy Pope** · 06-15-2009, 04:06 AM

Sub ColorChartPoints()
    
    Dim objCht As Chart
    Dim rngData As Range
    Dim lngIndex As Long
    Dim lngColor As Long
    
    Set rngData = Worksheets("Data Table").Range("D5:D19")
    Set objCht = Charts("Color Chart")
    With objCht
        With .SeriesCollection(1)
            For lngIndex = 1 To .Points.Count
                Select Case rngData.Cells(lngIndex, 1).Value
                Case "source1"
                    lngColor = RGB(0, 0, 255)
                Case "source2"
                    lngColor = RGB(0, 255, 0)
                Case "source3"
                    lngColor = RGB(255, 0, 0)
                Case "source4"
                    lngColor = RGB(50, 100, 200)
                Case Else
                    lngColor = RGB(0, 0, 0)
                End Select
                With .Points(lngIndex).Format.Fill
                    .ForeColor.RGB = lngColor
                    .BackColor.RGB = lngColor
                End With
            Next
        End With
    End With
End Sub

**JayUSA** · 06-15-2009, 09:10 PM

Hi Andy,

I am so grateful and impressed by your assistance that I just had to find and read your bio to learn who you are!

And then I had to go look-up the word "bespoke" -- as in "bespoke solutions"!

Anyway, as you can probably tell, I'm astonished by the degree of interest and assistance that continuously flows from you, Martin, DonkeyOte and the other forum heavy-hitters.

Anyway... back to business...

I was very successful in my effort to import the "bespoke" VBA code you kindly supplied, and it is up and running well in my spreadsheet -- beautiful!

Well, beautiful with a hitch -- the hitch is that my data table contains hundreds of rows of data, and I use Autofilters to identify the data I wish to plot at any time on my scatter chart.

Unfortunately, the code that you customized for me does not work properly with respect to plotting a subset of data using Autofilters. And I don't understand enough about the code's internal logic to hypothesize about the specific nature of the problem.

I really feel like you have already provided far more assistance than I could reasonably hope to receive -- but I am just wondering if there is an easy modification that will allow your code to live happily in my environment.

Lastly, though I'm sure you understand the issue I describe, you can see it in vivo by taking a peek at my updated sample spreadsheet. It's all very self-explanatory.

In any event, thanks again for your extra-ordinary help with this issue.

Cheers,

Jay

**Andy Pope** · 06-16-2009, 05:19 AM

The code requires a tweak in order to handle filtered range.

This change moves through the Areas and Cells of the visible range.

Sub ColorChartPoints()
    
    Dim objCht As Chart
    Dim rngData As Range
    Dim lngIndex As Long
    Dim lngColor As Long
    Dim lngCellIndex As Long
    Dim lngAreaIndex As Long
    
    Set rngData = Worksheets("Data Table").Range("D5:D19").SpecialCells(xlCellTypeVisible)
    Set objCht = Charts("Color Chart")
    lngCellIndex = lngCellIndex + 1
    lngAreaIndex = 1
    
    With objCht
        With .SeriesCollection(1)
            For lngIndex = 1 To .Points.Count
                Select Case rngData.Areas(lngAreaIndex).Cells(lngCellIndex).Value
                Case "source1"
                    lngColor = RGB(0, 0, 255)
                Case "source2"
                    lngColor = RGB(0, 255, 0)
                Case "source3"
                    lngColor = RGB(255, 0, 0)
                Case "source4"
                    lngColor = RGB(50, 100, 200)
                Case Else
                    lngColor = RGB(0, 0, 0)
                End Select
                
                With .Points(lngIndex).Format.Fill
                    .ForeColor.RGB = lngColor
                    .BackColor.RGB = lngColor
                End With
            
                lngCellIndex = lngCellIndex + 1
                If lngCellIndex > rngData.Areas(lngAreaIndex).Cells.Count Then
                    lngCellIndex = 1
                    lngAreaIndex = lngAreaIndex + 1
                End If
            
            Next
        End With
    End With
End Sub

**Bernadine** · 10-25-2012, 01:43 PM

Hi Andy,

Your code has helped me greatly. Thanks so much.
I got my scatter chart all color coded. Now I need to make a legend to show the meaning of each color. Do you have a code that make this legend?

Thanks So much.

Originally Posted by Andy Pope

Sub ColorChartPoints()
    
    Dim objCht As Chart
    Dim rngData As Range
    Dim lngIndex As Long
    Dim lngColor As Long
    
    Set rngData = Worksheets("Data Table").Range("D5:D19")
    Set objCht = Charts("Color Chart")
    With objCht
        With .SeriesCollection(1)
            For lngIndex = 1 To .Points.Count
                Select Case rngData.Cells(lngIndex, 1).Value
                Case "source1"
                    lngColor = RGB(0, 0, 255)
                Case "source2"
                    lngColor = RGB(0, 255, 0)
                Case "source3"
                    lngColor = RGB(255, 0, 0)
                Case "source4"
                    lngColor = RGB(50, 100, 200)
                Case Else
                    lngColor = RGB(0, 0, 0)
                End Select
                With .Points(lngIndex).Format.Fill
                    .ForeColor.RGB = lngColor
                    .BackColor.RGB = lngColor
                End With
            Next
        End With
    End With
End Sub