Bigger - Better - Brilliant!
Mansoor asked an interesting question for today's New Widescreen Podcast. Can you assign a Color to each Unique Value in a Column, and then automatically re-use that Color when the same Value is entered. Bill understands that in order to perform the required spec, this operation will require a VBA Event Handler Macro to analyze each entry. However, the logic is similar to what you would use in Excel: use =MATCH() to figure out if the Value appears above. If the Value is found, then re-use the Color from the matching Cell. Otherwise, create a random Color using three =RANDBETWEEN() Functions. Follow Bill in Episode #1718 to see how the result is achieved and the Color Values are applied.
Use Excel® 2013 VBA and Macros to automate virtually any routine task, and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! You'll discover macro techniques you won't find anywhere else and learn how to create automated reports that are amazingly powerful and useful. VBA and Macros Excel 2013
For more information on Excel 2010 VBA and Macros, check out...
VBA and Macros: Microsoft Excel 2010 - The fastest, best way to go beyond the Macro Recorder and move up the Excel VBA learning curve...Includes crucial information on making Excel 2010 VBA code work with older versions
Using Microsoft Excel 2010 VBA scripting features, Excel users can save dozens -- or even hundreds -- of hours per year. But most Excel users have never written a VBA script: many haven't even used Excel's built-in Macro Recorder. VBA and Macros: Microsoft Excel 2010
"The Learn Excel from MrExcel Podcast Series"
Visit us: MrExcel.com for all of your Microsoft Excel Needs!
Mansoor asked an interesting question for today's New Widescreen Podcast. Can you assign a Color to each Unique Value in a Column, and then automatically re-use that Color when the same Value is entered. Bill understands that in order to perform the required spec, this operation will require a VBA Event Handler Macro to analyze each entry. However, the logic is similar to what you would use in Excel: use =MATCH() to figure out if the Value appears above. If the Value is found, then re-use the Color from the matching Cell. Otherwise, create a random Color using three =RANDBETWEEN() Functions. Follow Bill in Episode #1718 to see how the result is achieved and the Color Values are applied.
Use Excel® 2013 VBA and Macros to automate virtually any routine task, and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! You'll discover macro techniques you won't find anywhere else and learn how to create automated reports that are amazingly powerful and useful. VBA and Macros Excel 2013
For more information on Excel 2010 VBA and Macros, check out...
VBA and Macros: Microsoft Excel 2010 - The fastest, best way to go beyond the Macro Recorder and move up the Excel VBA learning curve...Includes crucial information on making Excel 2010 VBA code work with older versions
Using Microsoft Excel 2010 VBA scripting features, Excel users can save dozens -- or even hundreds -- of hours per year. But most Excel users have never written a VBA script: many haven't even used Excel's built-in Macro Recorder. VBA and Macros: Microsoft Excel 2010
"The Learn Excel from MrExcel Podcast Series"
Visit us: MrExcel.com for all of your Microsoft Excel Needs!
Transcript of the video:
MrExcel podcast is sponsored by "Easy-XL"!
Learn Excel from MrExcel podcast, episode 1718 - Unique Color for Each Unique Value!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
After that holiday weekend in the United States, we're back here on Tuesday.
Want to send out congratulations to my youngest son Zeke, who graduated from high school, and my father Bob who turned 91 yesterday, had a big party, people were in from all over.
I never got to a podcast, Memorial Day podcast, but we're back here on Tuesday, with a question from Mansoor "How can I assign unique colors to every unique value within that column?" So you know, you put in "HV22220", comes up with one color type, another value, get another color type, another value, get another color.
But then you go back and type the same value that appeared before, it gets the same color as the previous.
Alright, so this is going to be a macro, obviously, and some things to think about, we want this to work in one particular column.
So I'm just going to, let's use here, column 3, and when data gets entered in some rows, let's say that we entered data here, think about what we have to do.
We have to look at that value that just got entered, and then do a =MATCH, and go look from C1 to C, over here I say that's N-1, but C1:C4.
So we're going to look above us, and see if the value that was just entered is there, and it was there, hey we're done, find that row, copy the interior, fill color down to this row.
But if it's not, then we have to assign a color, and this can get really difficult, figuring out what color to assign.
Now today, I'm just going to randomly choose a red value, a green value, blue value, and send it to the RGB function.
So I'm going to choose a number between 0-255 using RANDBETWEEN, but all of this is going to happen in the VBA window.
To get the VBA we press Alt+F11, over here in the Project Explorer, go to View, Project Explorer, Ctrl+R, you're going to look for this workbook, you're going to find Sheet1 and double click Sheet1.
Top left drop-down, there's only one thing to choose, choose Worksheet, and they automatically choose SelectionChange.
Now that's not what we want, we want to create a macro that runs every time that we Change the worksheet.
Alright, so we can actually get rid of this tiny little macro they created, they create the beginning and end for us.
I've already written this, and it's on the clipboard, so I'm just going to press Ctrl+V, and we're going to talk about this, one thing at a time, OK.
So remember, I said that I am eventually going to use a MATCH, and I'm going to use RANDBETWEEN RANDBETWEEN RANDBETWEEN.
So we actually have a situation where I'm using 4 different worksheet functions, and normally we would have to do something like x = Application.WorksheetFunction.Match, and that simply is a lot to type, especially for x.
So there's a trick, I don't know where I learned this trick from, but I saw someone using this trick, you can actually create an object variable called WF, you can call it whatever you want.
This stands for Worksheet Function, and set WF equal to Application.WorksheetFunction , and then once we've done that, I don't have to type Application.WorksheetFunction.Match, I can just say X = WF.Match, and go ahead and type everything.
So, think of it as a shorthand way, I'm too busy, lazy to type Application.WorksheetFunction over and over and over again.
OK, now these is a special kind of macro called an EventHandler macro, and it fires every time that someone has changed to cell in this worksheet.
They pass us a variable called Target, and Target is an object variable, it says what cell just changed.
And the thing I want to be careful of is, I don't want this to run if someone comes here and clears a bunch of cells, or you know, types 123 Enter.
I don't know how to deal with that, so right now it's only good for creating a single value.
So the first thing we do, Target that range that just changed, the number of Cells.Count, if it's equal to 1 then we keep going, we just let the macro not end, and check the target column.
Are we in column C?
Column C is the 3rd column, then we keep going, otherwise the macro ends.
And we learned this actually last Thursday's podcast, if you're going to do a Match, and the value is not found, it's going to throw an error which stops your macro.
So I'm going to avoid that with On Error Resume Next, but the Match won't get reset.
So we have to reset the variable to 0, and then do the Match, alright.
We're going to look for the Target.Value, that's that value we just typed, we're going to start in C1, and we're going to resize that down to the Target.Row.
Remember, Target is a cell that just changed, because if we just changed cell C99, Target.Row will be 99-1, gets us to 98.
And then always, in our Match with a 0, which is like using FALSE in VLOOKUP saying "We want an exact match!" Now I reset that area, and they're back to the normal operation, that's called a On Error GoTo 0, and then check if x > 0.
Ah great, this value has already been typed, the Interior.Color of the target cell just changed, is the same as the value in row X column 3 Interior.Color!
Now the 3 here and the 3 here is because I'm working in column C, the X was a result of the Match.
Easy, right?
And actually, you know, then I really start to think about choosing a new color, do we have a big huge list of colors we want to use?
For today I'm just going to random, which doesn't have the best results, sometimes we get really dark colors, you might get two colors that are the same.
RGB is a great function, you pass it a number from 0-255 for red, for green, for blue.
So we used Application.WorksheetFunction, but I didn't have to type the whole thing because I've already set up that object variable, WF means Application.WorksheetFunction, and used RandBetween.
Alright, so all of this is here, we'll switch back to Excel, and if I go to any other column and type a value, nothing happens, because it's not in the 3rd column.
If Target.Column is not equal to 3 then nothing happens anywhere but here.
OK, so we type HV22220, and we randomly get a color, and every time that I would do this, I'm going to get a different random color.
Alright, we'll do a ABC123, new color, GHI987, new color.
Although see, the other colors are similar.
That's what makes me think, maybe we should come up with some list of colors that we should loop through or, I don't know, do we use the old 56 colors from Excel 2003 that they're stored in the workbook?
I don't know, I'm not sure, but now when we go back and we type ABC123, the sheet EventHandler does the Match, and it sees this had already been used, HV22220, and it uses the color.
Let's type something else, RTY123, and we'll get a new color, repeat ABC123, the old color comes back.
Alright, now one of the one of the Gotcha's here.
Every time that you run a macro, it clears the Undo stack.
When you have a worksheet EventHandler running, well every time then we type something in this worksheet, it's going to clear the Undo stack.
So you, you know, effectively are killing Undo when you use this method.
But if you have a particular need, this might be a good way to go.
You know, to avoid that, rather than have a worksheet EventHandler, you could type all the values, and then run a macro, that would go through and analyze one cell at a time.
But for Mansoor, here this seems to be a good way to go.
And actually for anyone, even if you don't have this problem, a good tip today is just, if you're using Application.WorksheetFunction over and over and over again.
Set it to an object variable, set WF = Application.WorksheetFunction and it'll save you some time as you go through.
Alright hey, 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 1718 - Unique Color for Each Unique Value!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
After that holiday weekend in the United States, we're back here on Tuesday.
Want to send out congratulations to my youngest son Zeke, who graduated from high school, and my father Bob who turned 91 yesterday, had a big party, people were in from all over.
I never got to a podcast, Memorial Day podcast, but we're back here on Tuesday, with a question from Mansoor "How can I assign unique colors to every unique value within that column?" So you know, you put in "HV22220", comes up with one color type, another value, get another color type, another value, get another color.
But then you go back and type the same value that appeared before, it gets the same color as the previous.
Alright, so this is going to be a macro, obviously, and some things to think about, we want this to work in one particular column.
So I'm just going to, let's use here, column 3, and when data gets entered in some rows, let's say that we entered data here, think about what we have to do.
We have to look at that value that just got entered, and then do a =MATCH, and go look from C1 to C, over here I say that's N-1, but C1:C4.
So we're going to look above us, and see if the value that was just entered is there, and it was there, hey we're done, find that row, copy the interior, fill color down to this row.
But if it's not, then we have to assign a color, and this can get really difficult, figuring out what color to assign.
Now today, I'm just going to randomly choose a red value, a green value, blue value, and send it to the RGB function.
So I'm going to choose a number between 0-255 using RANDBETWEEN, but all of this is going to happen in the VBA window.
To get the VBA we press Alt+F11, over here in the Project Explorer, go to View, Project Explorer, Ctrl+R, you're going to look for this workbook, you're going to find Sheet1 and double click Sheet1.
Top left drop-down, there's only one thing to choose, choose Worksheet, and they automatically choose SelectionChange.
Now that's not what we want, we want to create a macro that runs every time that we Change the worksheet.
Alright, so we can actually get rid of this tiny little macro they created, they create the beginning and end for us.
I've already written this, and it's on the clipboard, so I'm just going to press Ctrl+V, and we're going to talk about this, one thing at a time, OK.
So remember, I said that I am eventually going to use a MATCH, and I'm going to use RANDBETWEEN RANDBETWEEN RANDBETWEEN.
So we actually have a situation where I'm using 4 different worksheet functions, and normally we would have to do something like x = Application.WorksheetFunction.Match, and that simply is a lot to type, especially for x.
So there's a trick, I don't know where I learned this trick from, but I saw someone using this trick, you can actually create an object variable called WF, you can call it whatever you want.
This stands for Worksheet Function, and set WF equal to Application.WorksheetFunction , and then once we've done that, I don't have to type Application.WorksheetFunction.Match, I can just say X = WF.Match, and go ahead and type everything.
So, think of it as a shorthand way, I'm too busy, lazy to type Application.WorksheetFunction over and over and over again.
OK, now these is a special kind of macro called an EventHandler macro, and it fires every time that someone has changed to cell in this worksheet.
They pass us a variable called Target, and Target is an object variable, it says what cell just changed.
And the thing I want to be careful of is, I don't want this to run if someone comes here and clears a bunch of cells, or you know, types 123 Enter.
I don't know how to deal with that, so right now it's only good for creating a single value.
So the first thing we do, Target that range that just changed, the number of Cells.Count, if it's equal to 1 then we keep going, we just let the macro not end, and check the target column.
Are we in column C?
Column C is the 3rd column, then we keep going, otherwise the macro ends.
And we learned this actually last Thursday's podcast, if you're going to do a Match, and the value is not found, it's going to throw an error which stops your macro.
So I'm going to avoid that with On Error Resume Next, but the Match won't get reset.
So we have to reset the variable to 0, and then do the Match, alright.
We're going to look for the Target.Value, that's that value we just typed, we're going to start in C1, and we're going to resize that down to the Target.Row.
Remember, Target is a cell that just changed, because if we just changed cell C99, Target.Row will be 99-1, gets us to 98.
And then always, in our Match with a 0, which is like using FALSE in VLOOKUP saying "We want an exact match!" Now I reset that area, and they're back to the normal operation, that's called a On Error GoTo 0, and then check if x > 0.
Ah great, this value has already been typed, the Interior.Color of the target cell just changed, is the same as the value in row X column 3 Interior.Color!
Now the 3 here and the 3 here is because I'm working in column C, the X was a result of the Match.
Easy, right?
And actually, you know, then I really start to think about choosing a new color, do we have a big huge list of colors we want to use?
For today I'm just going to random, which doesn't have the best results, sometimes we get really dark colors, you might get two colors that are the same.
RGB is a great function, you pass it a number from 0-255 for red, for green, for blue.
So we used Application.WorksheetFunction, but I didn't have to type the whole thing because I've already set up that object variable, WF means Application.WorksheetFunction, and used RandBetween.
Alright, so all of this is here, we'll switch back to Excel, and if I go to any other column and type a value, nothing happens, because it's not in the 3rd column.
If Target.Column is not equal to 3 then nothing happens anywhere but here.
OK, so we type HV22220, and we randomly get a color, and every time that I would do this, I'm going to get a different random color.
Alright, we'll do a ABC123, new color, GHI987, new color.
Although see, the other colors are similar.
That's what makes me think, maybe we should come up with some list of colors that we should loop through or, I don't know, do we use the old 56 colors from Excel 2003 that they're stored in the workbook?
I don't know, I'm not sure, but now when we go back and we type ABC123, the sheet EventHandler does the Match, and it sees this had already been used, HV22220, and it uses the color.
Let's type something else, RTY123, and we'll get a new color, repeat ABC123, the old color comes back.
Alright, now one of the one of the Gotcha's here.
Every time that you run a macro, it clears the Undo stack.
When you have a worksheet EventHandler running, well every time then we type something in this worksheet, it's going to clear the Undo stack.
So you, you know, effectively are killing Undo when you use this method.
But if you have a particular need, this might be a good way to go.
You know, to avoid that, rather than have a worksheet EventHandler, you could type all the values, and then run a macro, that would go through and analyze one cell at a time.
But for Mansoor, here this seems to be a good way to go.
And actually for anyone, even if you don't have this problem, a good tip today is just, if you're using Application.WorksheetFunction over and over and over again.
Set it to an object variable, set WF = Application.WorksheetFunction and it'll save you some time as you go through.
Alright hey, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel!
