Mike has 75,000 Data Points with three Values per Record. He wants to convert those Values to RGB and apply the color. This is an interesting proposition - but can it be done? Of course it can - this is Microsoft Excel! Today, in Episode #1633, Bill takes a look at the mechanics of RGB color and then shows us a six line Macro to convert the Values and apply RGB.
...This blog is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book! Power Excel With MrExcel - 2017 Edition
"The Learn Excel from MrExcel Podcast Series"
Visit us: MrExcel.com for all of your Microsoft Excel Needs!
...This blog is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book! Power Excel With MrExcel - 2017 Edition
"The Learn Excel from MrExcel Podcast Series"
Visit us: MrExcel.com for all of your Microsoft Excel Needs!
Transcript of the video:
This Excel podcast is sponsored by Easy-XL.
Learn Excel podcast episode 1633 apply RGB color based on cell values.
Hey welcome back to the MrExcel netcast I'm Bill Jelen and today questions sent in by Mike.
Mike works for one of the, I recognize the name, it's a big company that does colors for paint and I don't know if this is paint related or not but Mike has three columns of values and they run from, in my example, I just created a fake data set here, value A runs from one to a thousand, B from one to a thousand, C from one to five hundred and Mike wants to convert these to RGB colors and basically have a way to conditional format based on these values.
Well you know we're not going to be able to do this in the regular Excel interface but using VBA it would be relatively easy to do.
First though, let's talk about RGB.
When you try and use the fill color and come down here more colors and then the custom tab, all of the colors that you can create are based on this RGB model and the way that it works, you have values from 0 to 255 for red, green and blue.
So if we just did for 255 you would have a nice red color there.
If we did 0 red and 255 green of course you get green, and then 0 green and 255 blue you get the nice pure RGB blue there and you can do mixes.
So if we did half blue 127 and half red you get something in the purple or magenta range and using these 3 values you can create any of these colors.
So if I just randomly click here in a color to get this color you would use 75 red 53 green in 72 blue.
So to solve Mike's problem what we have to do is take these three values and scale them down to be somewhere between 0 and 255.
All right, so I'm going to switch over to VBA, I've already written the code to test it out.
Subroutine called add color for each cell in selection, so we're going to go ahead and select all of our rows of data just one call and then for the red value we're going to take that cell value the first column of data, divided by a thousand, multiplied by 255 and rounded to the nearest integer.
For green we're going to go cell offset one column to the right, divided by a thousand, times 255.
For blue though that third column because there it only ranges from 1 to 500 we're going to change to use divided by 500 times 255.
So here we're using the selected column and then one column to the right based on that offset and then two columns to the right so that's going to give us an R a G and a B and then you know I thought it would be interesting to be able to sort by the RGB value and looking down here in the immediate window you can see that I've done a few tests, if you ask for the RGB have full red 255 red 0, 0 it just gets passed through it's red times 1 but when you look for the G, so this is no red no blue one G, it's the green value times 256 and then the blue value times 65 536 so you know this is a binary progression here if you ask for the full 255 of RG and B and then you get the 16 point seven million possible colors you can create using this color scheme so I thought it might be interesting to get this number and so I put that out in the third column to the right of the selection and then I take the whole row so whatever that cell dot row is six columns, so one row by six columns dot interior dot color is equal to the RGB of our G and B.
All right so let's try this we'll come back here to Excel, I'll select my entire range of the first value so CTRL SHIFT down arrow and then we'll switch back here to VBA and we'll let this run.
Launching up here to see when the running goes away wow that was fast, all right let's go back and see what we have.
So there we go, they assigned a color value based on these static values.
Now it's really important that there's static values because if these values change then we're not going to see a change in the color you have to run the macro anytime you do it so let's see what would happen if we sort based on the RGB function value and it's like page down you know I sort of see bands of color but it's going from it's just rotating so that really doesn't seem to be very helpful the other way to sort of course is to sort by the A, B and C so let's do that.
First where we sort by the A value, add a level, sort by the B value, add a level, sort by the C value and click OK.
Here I’ll make this change the zoom so we can see more records at a time or you can see here at the top we definitely have a lot of blue values as we go down through our 5,000 records, although, it does alternate from light to dark light to dark the colors definitely seem to change towards the Reds.
All right so, is this going to be helpful for Mike, hard to say.
I'm not sure what his data really represents but it is an interesting way to quickly, you know, apply any one of 16 million colors based on those values using this tiny bit of VBA.
Well I want to thank Mike for sending that question in and I want to thank you for stopping by, we’ll see you next time for another podcast with MrExcel.
Learn Excel podcast episode 1633 apply RGB color based on cell values.
Hey welcome back to the MrExcel netcast I'm Bill Jelen and today questions sent in by Mike.
Mike works for one of the, I recognize the name, it's a big company that does colors for paint and I don't know if this is paint related or not but Mike has three columns of values and they run from, in my example, I just created a fake data set here, value A runs from one to a thousand, B from one to a thousand, C from one to five hundred and Mike wants to convert these to RGB colors and basically have a way to conditional format based on these values.
Well you know we're not going to be able to do this in the regular Excel interface but using VBA it would be relatively easy to do.
First though, let's talk about RGB.
When you try and use the fill color and come down here more colors and then the custom tab, all of the colors that you can create are based on this RGB model and the way that it works, you have values from 0 to 255 for red, green and blue.
So if we just did for 255 you would have a nice red color there.
If we did 0 red and 255 green of course you get green, and then 0 green and 255 blue you get the nice pure RGB blue there and you can do mixes.
So if we did half blue 127 and half red you get something in the purple or magenta range and using these 3 values you can create any of these colors.
So if I just randomly click here in a color to get this color you would use 75 red 53 green in 72 blue.
So to solve Mike's problem what we have to do is take these three values and scale them down to be somewhere between 0 and 255.
All right, so I'm going to switch over to VBA, I've already written the code to test it out.
Subroutine called add color for each cell in selection, so we're going to go ahead and select all of our rows of data just one call and then for the red value we're going to take that cell value the first column of data, divided by a thousand, multiplied by 255 and rounded to the nearest integer.
For green we're going to go cell offset one column to the right, divided by a thousand, times 255.
For blue though that third column because there it only ranges from 1 to 500 we're going to change to use divided by 500 times 255.
So here we're using the selected column and then one column to the right based on that offset and then two columns to the right so that's going to give us an R a G and a B and then you know I thought it would be interesting to be able to sort by the RGB value and looking down here in the immediate window you can see that I've done a few tests, if you ask for the RGB have full red 255 red 0, 0 it just gets passed through it's red times 1 but when you look for the G, so this is no red no blue one G, it's the green value times 256 and then the blue value times 65 536 so you know this is a binary progression here if you ask for the full 255 of RG and B and then you get the 16 point seven million possible colors you can create using this color scheme so I thought it might be interesting to get this number and so I put that out in the third column to the right of the selection and then I take the whole row so whatever that cell dot row is six columns, so one row by six columns dot interior dot color is equal to the RGB of our G and B.
All right so let's try this we'll come back here to Excel, I'll select my entire range of the first value so CTRL SHIFT down arrow and then we'll switch back here to VBA and we'll let this run.
Launching up here to see when the running goes away wow that was fast, all right let's go back and see what we have.
So there we go, they assigned a color value based on these static values.
Now it's really important that there's static values because if these values change then we're not going to see a change in the color you have to run the macro anytime you do it so let's see what would happen if we sort based on the RGB function value and it's like page down you know I sort of see bands of color but it's going from it's just rotating so that really doesn't seem to be very helpful the other way to sort of course is to sort by the A, B and C so let's do that.
First where we sort by the A value, add a level, sort by the B value, add a level, sort by the C value and click OK.
Here I’ll make this change the zoom so we can see more records at a time or you can see here at the top we definitely have a lot of blue values as we go down through our 5,000 records, although, it does alternate from light to dark light to dark the colors definitely seem to change towards the Reds.
All right so, is this going to be helpful for Mike, hard to say.
I'm not sure what his data really represents but it is an interesting way to quickly, you know, apply any one of 16 million colors based on those values using this tiny bit of VBA.
Well I want to thank Mike for sending that question in and I want to thank you for stopping by, we’ll see you next time for another podcast with MrExcel.
