Change the bubble colour based on a fourth value

  • Thread starter Thread starter Wayne
  • Start date Start date
W

Wayne

Hi,

Can anyone help me with some code. I have a bubble chart
with three ranges (x-axis, y-axis and size). I also have a
fourth column that has either a D or M in it. What I want
to do is, for each point, make the bubble colour orange
where the fourth column value is D and green where the
value is M.

Cheers

Wayne.
 
Hi Wayne -

You can adapt the Conditional Chart example on my web site to bubble charts:

http://peltiertech.com/Excel/Charts/ConditionalChart1.html

The other alternative is to use a macro to change the color point by point.

Sub ColorPoints()
Dim i As Integer
Dim myRange As Range
Dim iColor As Integer
Set myRange = ActiveSheet.Range("F9:F11")
For i = 1 To myRange.Rows.Count
Select Case WorksheetFunction.Index(myRange, i).Value
Case "M"
iColor = 4 ' green
Case "D"
iColor = 46 ' orange
End Select
ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1). _
Points(i).Interior.ColorIndex = iColor
Next
End Sub

- Jon
 
Thanks Jon
-----Original Message-----
Hi Wayne -

You can adapt the Conditional Chart example on my web site to bubble charts:

http://peltiertech.com/Excel/Charts/ConditionalChart1.html

The other alternative is to use a macro to change the color point by point.

Sub ColorPoints()
Dim i As Integer
Dim myRange As Range
Dim iColor As Integer
Set myRange = ActiveSheet.Range("F9:F11")
For i = 1 To myRange.Rows.Count
Select Case WorksheetFunction.Index(myRange, i).Value
Case "M"
iColor = 4 ' green
Case "D"
iColor = 46 ' orange
End Select
Click to expand...
ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1). _
 
afternoon all,

i have a bubble chart and the code posted in an earlier response t
this thread.

the code:

ub ColorPoints2()
Dim i As Integer
Dim myRange As Range
Dim iColor As Integer
Set myRange = ActiveSheet.Range("H28:H36")
For i = 1 To myRange.Rows.Count
Select Case WorksheetFunction.Index(myRange, i).Value
Case "Red"
iColor = 3 ' green
Case "Amber"
iColor = 0 ' orange
End Select
ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1). _
Points(i).Interior.ColorIndex = iColor
Next
End Sub

when run this works - the red bubbles are red and the amber bubbles ar
white.

here is the issue:

if i replace the "0" with a number (for example 6) then all the bubble
are coloured in yellow - even the ones that should be red.

Does anyone have any ideas?

Thanks,

MoonWeaze
 
Back
Top