Change color of data points in a chart in excel using VBA

00:40 Method 1: Change data point color based on categories
06:34 Method 2: Change data point color based on numbers
Sub color_dots()
Dim s As Series
Dim i As Integer
Set s = ActiveChart.FullSeriesCollection(1)
For i = 1 To s.Points.Count
If Cells(i + 1, 3) = "A" Then s.Points(i).Format.Fill.ForeColor.RGB = RGB(255, 80, 80)
If Cells(i + 1, 3) = "B" Then s.Points(i).Format.Fill.ForeColor.RGB = RGB(255, 153, 0)
If Cells(i + 1, 3) = "C" Then s.Points(i).Format.Fill.ForeColor.RGB = RGB(0, 128, 128)
Next i
End Sub
----------------------------------------------------------------------------------------------------------------
Sub color_dots()
Dim s As Series
Dim i As Integer
Set s = ActiveChart.FullSeriesCollection(1)
For i = 1 To s.Points.Count
s.Points(i).Format.Fill.ForeColor.RGB = Cells(i + 1, 4).DisplayFormat.Interior.Color
Next i
End Sub

[@ricardopina6027]

I spent an entire morning searching for exactly this directions on assigning a conditional formatting to represent a third value on a scattered chart. Super useful. Thank you

[@SuperWhizy]

Thank you. Outstanding instructions; concise, thorough and methodically executed. Love how you commented the script as well.

[@lingvoclub]

本当にありがとうございました！ あなたは、インターネットの広大な面でexcelの最高の先生です！

[@MrChrisd015]

Absolutely brilliant. So much easier, and with fewer steps, than all the other methods I've seen on other forums.

[@mikepike7063]

Brilliantly presented Karina , functional , concise results along the way , and lots of helpful tips to reduce macro generated code which is always excessive imho but each excess might be what people are looking for so no complaints. A* on everything and many thanks to you.

[@mohieel-dinsaber6427]

Hello, how do do the same but in a workbook with multiple worksheets. I mean how to pinpoint the column I want to use for the color scale, as there are multiple worksheets each with different columns. thanks in advance!!

[@Mathematicalbello]

Beautiful and very instructive, thanks a 1000 times

[@benjaminvanschoors1930]

When i try to run the same code as you do at 3:34 I get the following error: Object variable not set (Error 91). Any idea what I do wrong?

[@DebashishP82]

I get the same error

[@rudicerny]

Thank you, very useful. There only few such instructions on net, even if this essential for effective data presentation, congratulation!

[@anthonysingh-tf7vl]

How to do this with data located in separate worksheet from the plot??

[@seanbeers9217]

This was very helpful. Thank you!

[@mollym2000]

Karina, do you know if/how it is possible to have different data marker SHAPES in the same data series?

[@TheQuesssito]

i have the same question, do u know now??

[@anthoneliassen7707]

@@TheQuesssito Yes, it quite simple actually. Here is an example: s.Points(i).MarkerStyle = xlMarkerStyleX Just search for vba charts markerstyle to find alternative markerstyles.

[@dr.rohitmittal1270]

Thank You for such a useful information

[@latenightrelaxing6679]

thank you for sharing, that helped me a lot!

[@hshrestha2811]

this is genius.

[@suebkostick2040]

fantatsic!

[@annathevlogger8361]

Satamaika