MACRO - Change Color of Bubble Chart Points Based on Values

[mte498]

Hello all,

I am trying to change the colors on an excel bubble chart based on specific values. I ran across the below video and it's ALMOST exactly what I need.

https://www.youtube.com/watch?v=nEOjcyGh1O0

I unfortunately have way more color combos than just the Red, Green, Blue that are in the video. My solution has been to randomly assign each data point a number that corresponds to a single RGB color combo. For example, RGB combo (1,1,1) is assigned the number 1, RGB combo (1,2,1) is assigned the number 2, etc. On the chart's data, instead of Red, Green, Blue, I will use excel's Random function to randomly assign a number that is within the range of values on the RGB combos.

What's the best way to use these numbers as the chart's color driver vs. the Red, Green, Blue?

Please let me know if anything is unclear. Confusing, I know.

Thank you!
Mike

 

[Worf]

This is it:

Sub BB()
Dim cht As Chart, ser As Series, r As Range, i%
ActiveSheet.ChartObjects("Chart 9").Activate       ' your chart name here
Set cht = ActiveChart
Set ser = cht.SeriesCollection(1)
ser.HasDataLabels = True
ser.DataLabels.Position = xlLabelPositionCenter
ser.Format.Fill.TwoColorGradient msoGradientFromCenter, 1
For i = ser.Format.Fill.GradientStops.Count To 3 Step -1
    ser.Format.Fill.GradientStops.Delete
Next
ser.Format.Fill.ForeColor.RGB = RGB(250, 250, 250)  ' white
Set r = [a78]                                       ' starting RGB cell
For i = 1 To ser.Points.Count
    ser.Points(i).Format.Fill.GradientStops.Insert r, 0.95
    With ser.DataLabels(i).Format.TextFrame2.TextRange
        .Characters.Text = r.Offset(1)
        .Font.Size = 11
    End With
    Set r = r.Offset(, 1)
Next
End Sub