Jordan from MinusDesign sends in today's question: "How can you mix two icon sets in the same range?" Jordan wants to combined the green, yellow, red traffic lights with the pink, grey, and black traffic lights. Today, in Episode #1377, I harken way back to a Feb 2006 blog posting by David Gainer to solve this one.
...This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
...This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
MrExcel podcast is sposored by Easy-XL.
Learn Excel from MrExcel Podcast. Episode 1377- Mix 2 Icon Sets Hey, welcome back. I'm Bill Jelen from MrExcel.
Today's question sent in by Jordan.
Now Jordan has a great blog out here at minus design.
Jordan sent in this question.
He wrote a nice blog post on Conditional Formatting Traffic Lights where he used some IF statements to depending on words in another cell he got the right traffic light to show up, but Jordan wanted to mixe green yellow red and also, the pink black and gray.
Which are in two different icon sets and normally you can't do this.
But it's kind of excited when I read Jordan's post because I know I know there's a way to do it.
I couldn't remember that was all the way back from February 2006.
Dave Gayner who is writing the Excel team blog posted just a tiny little solution to this and Microsoft has since taken it down because it's about excel 2007.
But luckily someone somewhere stole it and copied it. I was able to find that code again So what we're trying to do is we're trying to mix this icon set with this icon set so let's take a look at how we're going to set that up.
First thing we're going to do.
Choose the range where we want to apply this.
These are the colors I want to use and the 6 is green 5 is yellow 4 is red and then switch over to the other icon set the pink the gray and the black.
As we have in a sense 6 different colors, so I'll go to Conditional Formatting Icon Sets and we will apply the second one the red yellow black the lower numbers first and then come back in Conditional Formatting Icon Sets and green yellow red and as Jordan discovered in his note to me the first one gets overwritten and the second one is the only one that shows up. It's actually backwards.
It's weird the way it works.I'm going to go to Conditional Formatting Manage Rules and here's what you'll see is the one that we said it most recently appears on top and that's the one that we're seeing.
Now couple of adjustments we want to do here. First thing is go in and Edit the Rule and we want these to not be percentages, but absolute numbers and we're gonna say let's see this red one. I'm never going to use.
I'm just going to use an incredibly high number here. If it's greater than 8 okay, but the pink one the pink one I want to have show up when it's 3 and gray when it's 2. That leaves the black one to show up when it's 1.
All right now bear with me.
We're going to get there.
Edit the Rule Again will change these three numbers This should be for 6 and that'd be for 5.
Click OK Click OK Okay, alright. So now we're getting the greens and yellows where they should be. Everything else is showing up as red.
That's not for 1.
All right now the Dave Gayner trick this has to be something they were going to put in the excel interface.
They coded it up in the VBA interface and then decided not to put it in the excel interface, but it's still left there in VBA. This is amazing. We have to notice that we are in cell E1 E1 is the active cell right there because the code that I'm going to use so I'm going to press Alt F11 or if you have the developer tab just visual basic Press Ctrl+ G. Ctrl+G will get you to the immediate window and you're going to type something like this Selection.FormatConditions(1) That's the first one in the list the green yellow red (1). Formula Now there's no way to do this in the excel interface, and I write a formula here that says hey, " IF (E1>3,True, False)" Press enter there and we'll go back to excel and [ inaudiable ], we're getting the Green for 6 the yellow for 5 red for 4.
but then if it's not greater than 3.
It stops using that first condition and the second condition shows through.
Now I think the cool effect here because Jordan was trying to not really show the numbers you just wanted to show the icon for each of these if we manage rules Edit Rule and say Show Icon Only We should have done that ofcourse the first time through, but I wanted to see how this was working Click OK All right so now you have your formulas here by the way, it'll actually center the icon now And very cool way to go. Thank Jordan for sending that question in and thank David Gaynor formally project manager for excel for posting that way back in 2006 very very cool trick.
Okay, all right. I want to thank for stopping by. See you next time for another netcast from MrExcel.
Learn Excel from MrExcel Podcast. Episode 1377- Mix 2 Icon Sets Hey, welcome back. I'm Bill Jelen from MrExcel.
Today's question sent in by Jordan.
Now Jordan has a great blog out here at minus design.
Jordan sent in this question.
He wrote a nice blog post on Conditional Formatting Traffic Lights where he used some IF statements to depending on words in another cell he got the right traffic light to show up, but Jordan wanted to mixe green yellow red and also, the pink black and gray.
Which are in two different icon sets and normally you can't do this.
But it's kind of excited when I read Jordan's post because I know I know there's a way to do it.
I couldn't remember that was all the way back from February 2006.
Dave Gayner who is writing the Excel team blog posted just a tiny little solution to this and Microsoft has since taken it down because it's about excel 2007.
But luckily someone somewhere stole it and copied it. I was able to find that code again So what we're trying to do is we're trying to mix this icon set with this icon set so let's take a look at how we're going to set that up.
First thing we're going to do.
Choose the range where we want to apply this.
These are the colors I want to use and the 6 is green 5 is yellow 4 is red and then switch over to the other icon set the pink the gray and the black.
As we have in a sense 6 different colors, so I'll go to Conditional Formatting Icon Sets and we will apply the second one the red yellow black the lower numbers first and then come back in Conditional Formatting Icon Sets and green yellow red and as Jordan discovered in his note to me the first one gets overwritten and the second one is the only one that shows up. It's actually backwards.
It's weird the way it works.I'm going to go to Conditional Formatting Manage Rules and here's what you'll see is the one that we said it most recently appears on top and that's the one that we're seeing.
Now couple of adjustments we want to do here. First thing is go in and Edit the Rule and we want these to not be percentages, but absolute numbers and we're gonna say let's see this red one. I'm never going to use.
I'm just going to use an incredibly high number here. If it's greater than 8 okay, but the pink one the pink one I want to have show up when it's 3 and gray when it's 2. That leaves the black one to show up when it's 1.
All right now bear with me.
We're going to get there.
Edit the Rule Again will change these three numbers This should be for 6 and that'd be for 5.
Click OK Click OK Okay, alright. So now we're getting the greens and yellows where they should be. Everything else is showing up as red.
That's not for 1.
All right now the Dave Gayner trick this has to be something they were going to put in the excel interface.
They coded it up in the VBA interface and then decided not to put it in the excel interface, but it's still left there in VBA. This is amazing. We have to notice that we are in cell E1 E1 is the active cell right there because the code that I'm going to use so I'm going to press Alt F11 or if you have the developer tab just visual basic Press Ctrl+ G. Ctrl+G will get you to the immediate window and you're going to type something like this Selection.FormatConditions(1) That's the first one in the list the green yellow red (1). Formula Now there's no way to do this in the excel interface, and I write a formula here that says hey, " IF (E1>3,True, False)" Press enter there and we'll go back to excel and [ inaudiable ], we're getting the Green for 6 the yellow for 5 red for 4.
but then if it's not greater than 3.
It stops using that first condition and the second condition shows through.
Now I think the cool effect here because Jordan was trying to not really show the numbers you just wanted to show the icon for each of these if we manage rules Edit Rule and say Show Icon Only We should have done that ofcourse the first time through, but I wanted to see how this was working Click OK All right so now you have your formulas here by the way, it'll actually center the icon now And very cool way to go. Thank Jordan for sending that question in and thank David Gaynor formally project manager for excel for posting that way back in 2006 very very cool trick.
Okay, all right. I want to thank for stopping by. See you next time for another netcast from MrExcel.
