In MS Excel, how can I create a bubble chart where the color of the bubbles match the color of a cell?

Question

I would like to illustrate a fourth variable (x axis, y axis, bubble size, bubble color), but do not want to artificially split the series into pre-defined groups based on a set of thresholds. Rather, I want each bubble to reflect the particular colors of a cell (which will be colored through conditional formatting based on the fourth variable).

Anyone know how to do this?

Solution

How it works:

1. Enter data & Fill colors.
2. click Insert, Insert Scatter (X, Y) or Bubble chart, and select bubble chart.
3. Right click at the chart and click Select Data from the menu.
4. From the Select Data Source Click Add & select relative cells for Data Series .
5. For First text box select cell A1 & below cells for other Text Boxes like A2, A3, A4.
6. Repeat step 5 for Column B & C and Finish with Ok.

7. Copy & Paste this VB code as Standard Module with the Sheet.

   Option Explicit
   
   Sub ColorChartSeries()
   Dim iRow As Long, iCol As Long
   Dim theBubbles As Range
   Dim theChart As Chart
   Dim theSeries As Series
   Dim thePoint As Point
   
   Set theChart = ActiveChart
   
   If (theChart.ChartType <> xlBubble And theChart.ChartType <> xlBubble3DEffect) Then
       MsgBox "This works only for bubble charts!"
       End
   End If
   
   For Each theSeries In theChart.SeriesCollection
       Set theBubbles = Range(theSeries.BubbleSizes)
       iRow = theBubbles.Row - 1
       iCol = theBubbles.Column
       For Each thePoint In theSeries.Points
           iRow = iRow + 1
           thePoint.Format.Fill.ForeColor.RGB = Cells(iRow, iCol).Interior.Color
       Next thePoint
   Next theSeries
   
   End Sub
   

8. Finally, select the Chart Area & RUN the Code.

You get the Bubble Colors similar as the Filled Cell colors.

Note, This method never gets the Conditionally Formatted Cell Colors.

Comments

[user3661678]

To get the conditionally formatted cell colors just use Cells(iRow, iCol).DisplayFormat.Interior.Color instead of Cells(iRow, iCol).Interior.Color :)