Tony wants to assign colors to each bubble in a chart based on text he has in a column in Excel. Learn how to use the macro recorder to learn the correct properties for adjusting the bubble color. Also - this is beyond what Tony asked, but the macro also correctly labels each bubble using text in column A.
Transcript of the video:
MrExcel podcast is sponsored by "Easy-XL"!
Learn Excel from MrExcel podcast, episode 1757 - Bubble Chart - Color Each Bubble!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Got a call from my friend Tony this week, he was trying to color each individual bubble in a bubble chart, based on a column that he had out here.
Well, bubble charts, they're kind of a strange animal in Excel, because we have to use all three columns to communicate, the X value, the Y value, and the size.
And so, when we look up here in the Series formula, wherever we would normally store the label information, they have to store the size information.
So it's an unusual type of chart to begin with, it's hard to label.
Usually we have to go out and download the chart label or add-in, but let's knock out a little bit of macro code here.
So I said to Tony, I said "Hey, you know, I'm at the grocery store right now, just try this:" Turn on the macro recorder, call it HowToColorBubble, and select the chart.
The first click on a bubble will select the entire series, and you can see this in the Layout tab that we selected Series 1.
Then do a second click on one bubble to select that one point, and look up here in this drop-down to see how they label it.
And I was hoping they were going to get the label from here, but of course they can't, because they don't even know that these are the chart labels.
Instead they're numbering them based on the numbers here, which is really bad, because you might have duplicates, and you might have formulas here which I do, I have random formulas, so these numbers are going to change all the time, there's no way that I can figure out the point name based on data in this column.
OK, so now we've selected that, and let's apply a color down here, I'm going to use one of the standard colors instead of the theme colors, and we will stop recording.
Go look at that code so Alt+F8, and HowToColorBubble, click Edit.
Alright, so they activate the charts, select the first series, this is really good!
So even though it was called Point 76, or whatever in quotes, they're actually just using index numbers here, so Point 1-10 corresponds to the rows, apparently.
And then this is really why I recorded the macro: So we have the series, we have the point, and then we need .Format.Fill , .ForeColor.RGB , is equal to the actual color, so that is crazy!
We’re going to have Ser.Points , and there will be a loop, .Format.Fill.ForeColor.RGB , that is an incredible string of properties there, that I never would have been able to remember or figure out without the macro recorder.
So the whole point of recording this macro is to get that one line of code right there, we select the series, select the points, and then .Format.Fill.ForeColor.RGB . I don't need this macro, I can actually delete the module because I have this line of code, and we'll use that line of code back here, in the macro that I started.
Now some of this code I got from my book "Charts and Graphs" for Excel 2010, or Excel 2013, or even Excel 2007.
I have a chapter there on macros for charts.
So I set up two object variables, Chart and Series, Set Chart = ActiveChart after activating, and then SeriesCollection(1) , there's only one series, figure out how many points we have.
I'm going to add labels, even though Tony didn't want to add labels, I figured that this would be a good time to do this.
And then we loop For i = 1 To PointCount , figure out the color from the sheet, so we look at cells, i + 1, that's the row number.
Why do we look at i + 1 ? Because if you remember, there are headings up in row 1, so 0.1 is going to be in row 2.
And the column is column 5, so that's column E, get that value.
Tony said that he was going to have a Select Case in there, so we look at, you know, if it's red or blue or green.
Then here's that line of code, so Ser.Points(i).Format.Fill.ForeColor.RGB , all of that came from the recorded macro, is equal to, and I use the RGB function because i can figure out the RGB code.
So red is just 255 in the red parameter, blue is 255 in the blue parameter, green 255.
Now these are kind of ugly colors, especially the green, you can go get your own RGB mix of codes, and put them in there.
And then I also said "Hey, let's go ahead and label the point, with the value from column A." So Ser.Points(i).DataLabel.Text , how did I figure this out?
I turned on the macro recorder, added labels, selected one label, and changed it to something else, just to see that it was .DataLabel.Text . Alright so here, let's take a look, here's our chart right now, here are the colors we want to use.
We run this macro, and we now have the colors based on the colors here.
And if later on we would change to a different color, we just have to come back and rerun the macro to reapply those colors.
Alright well hey, I want to thank Tony for sending that great question in, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel!
Learn Excel from MrExcel podcast, episode 1757 - Bubble Chart - Color Each Bubble!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Got a call from my friend Tony this week, he was trying to color each individual bubble in a bubble chart, based on a column that he had out here.
Well, bubble charts, they're kind of a strange animal in Excel, because we have to use all three columns to communicate, the X value, the Y value, and the size.
And so, when we look up here in the Series formula, wherever we would normally store the label information, they have to store the size information.
So it's an unusual type of chart to begin with, it's hard to label.
Usually we have to go out and download the chart label or add-in, but let's knock out a little bit of macro code here.
So I said to Tony, I said "Hey, you know, I'm at the grocery store right now, just try this:" Turn on the macro recorder, call it HowToColorBubble, and select the chart.
The first click on a bubble will select the entire series, and you can see this in the Layout tab that we selected Series 1.
Then do a second click on one bubble to select that one point, and look up here in this drop-down to see how they label it.
And I was hoping they were going to get the label from here, but of course they can't, because they don't even know that these are the chart labels.
Instead they're numbering them based on the numbers here, which is really bad, because you might have duplicates, and you might have formulas here which I do, I have random formulas, so these numbers are going to change all the time, there's no way that I can figure out the point name based on data in this column.
OK, so now we've selected that, and let's apply a color down here, I'm going to use one of the standard colors instead of the theme colors, and we will stop recording.
Go look at that code so Alt+F8, and HowToColorBubble, click Edit.
Alright, so they activate the charts, select the first series, this is really good!
So even though it was called Point 76, or whatever in quotes, they're actually just using index numbers here, so Point 1-10 corresponds to the rows, apparently.
And then this is really why I recorded the macro: So we have the series, we have the point, and then we need .Format.Fill , .ForeColor.RGB , is equal to the actual color, so that is crazy!
We’re going to have Ser.Points , and there will be a loop, .Format.Fill.ForeColor.RGB , that is an incredible string of properties there, that I never would have been able to remember or figure out without the macro recorder.
So the whole point of recording this macro is to get that one line of code right there, we select the series, select the points, and then .Format.Fill.ForeColor.RGB . I don't need this macro, I can actually delete the module because I have this line of code, and we'll use that line of code back here, in the macro that I started.
Now some of this code I got from my book "Charts and Graphs" for Excel 2010, or Excel 2013, or even Excel 2007.
I have a chapter there on macros for charts.
So I set up two object variables, Chart and Series, Set Chart = ActiveChart after activating, and then SeriesCollection(1) , there's only one series, figure out how many points we have.
I'm going to add labels, even though Tony didn't want to add labels, I figured that this would be a good time to do this.
And then we loop For i = 1 To PointCount , figure out the color from the sheet, so we look at cells, i + 1, that's the row number.
Why do we look at i + 1 ? Because if you remember, there are headings up in row 1, so 0.1 is going to be in row 2.
And the column is column 5, so that's column E, get that value.
Tony said that he was going to have a Select Case in there, so we look at, you know, if it's red or blue or green.
Then here's that line of code, so Ser.Points(i).Format.Fill.ForeColor.RGB , all of that came from the recorded macro, is equal to, and I use the RGB function because i can figure out the RGB code.
So red is just 255 in the red parameter, blue is 255 in the blue parameter, green 255.
Now these are kind of ugly colors, especially the green, you can go get your own RGB mix of codes, and put them in there.
And then I also said "Hey, let's go ahead and label the point, with the value from column A." So Ser.Points(i).DataLabel.Text , how did I figure this out?
I turned on the macro recorder, added labels, selected one label, and changed it to something else, just to see that it was .DataLabel.Text . Alright so here, let's take a look, here's our chart right now, here are the colors we want to use.
We run this macro, and we now have the colors based on the colors here.
And if later on we would change to a different color, we just have to come back and rerun the macro to reapply those colors.
Alright well hey, I want to thank Tony for sending that great question in, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel!
