Bubble charts - Different color for each frequency count

[Neeraj]

I have made a bubble chart with 3 parameters, Number of products used by the customers, avg monthly revenue and no of years since he is with the company. I want that the colors of bubbles be different for different product count i.e. if a bubble represents 1 product, then it be of diff color, diff one for 2 product and so on. Hence for each product-frequency, the color should be different.


Pls advise how to do this (Can be a VBA solution, but preferably a non-vba solution).


The sample excel file is posted at

http://www.2shared.com/file/aLQlzZk2/BubbleCharts.html

 

[Hui]

This can be done with VBA or Manually


Manually

To color each series according to Product Count you will need multiple series.

So add series for each set of data using +na() for missing data

I've included your data as a sample here: https://rapidshare.com/files/3286688914/BubbleCharts.xlsm


Or


Here is a VBA Solution

I shifted the chart to the worksheet first

Then copy/paste the code into a code module in VBA (Alt F11)

[pre]

Sub ColorMyBubbles()
Dim Values As Variant
Dim XValues As Variant

Clr1 = RGB(255, 0, 0) 'Red
Clr2 = RGB(255, 100, 0) 'Orange
Clr3 = RGB(255, 255, 0) 'Yellow
Clr4 = RGB(0, 255, 0) 'Green
Clr5 = RGB(0, 255, 100) 'Pale Green
Clr6 = RGB(0, 255, 255) 'Pale Blue
Clr7 = RGB(0, 0, 255) 'Blue
Clr8 = RGB(200, 0, 255) 'Purple
Clr9 = RGB(100, 100, 255) 'Medium Blue
Clr10 = RGB(0, 0, 0) 'Black

ActiveSheet.ChartObjects("Chart 1").Activate
With ActiveChart.SeriesCollection(1)
Values = .Values
For pt = 1 To UBound(Values)
Select Case Values(pt)
Case 1
ActiveChart.SeriesCollection(1).Points(pt).Format.Fill.ForeColor.RGB = Clr1
Case 2
ActiveChart.SeriesCollection(1).Points(pt).Format.Fill.ForeColor.RGB = Clr2
Case 3
ActiveChart.SeriesCollection(1).Points(pt).Format.Fill.ForeColor.RGB = Clr3
Case 4
ActiveChart.SeriesCollection(1).Points(pt).Format.Fill.ForeColor.RGB = Clr4
Case 5
ActiveChart.SeriesCollection(1).Points(pt).Format.Fill.ForeColor.RGB = Clr5
Case 6
ActiveChart.SeriesCollection(1).Points(pt).Format.Fill.ForeColor.RGB = Clr6
Case 7
ActiveChart.SeriesCollection(1).Points(pt).Format.Fill.ForeColor.RGB = Clr7
Case 8
ActiveChart.SeriesCollection(1).Points(pt).Format.Fill.ForeColor.RGB = Clr8
Case 9
ActiveChart.SeriesCollection(1).Points(pt).Format.Fill.ForeColor.RGB = Clr9
Case Else
ActiveChart.SeriesCollection(1).Points(pt).Format.Fill.ForeColor.RGB = Clr10
End Select
Next

End With

End Sub

[/pre]