Melissa wants to apply a color to values in the 1000-1999, 2000-2999, and so on ranges. This Dueling Excel podcast comes down to a question of what did Melissa really mean by her question. Bill does a VBA Macro to color all bands. Mike does an input cell and conditional formatting to highlight one chosen band.
Transcript of the video:
Hey, alright, it's time for another Dueling Excel podcast.
I'm Bill Jelen from MrExcel, I’ll be joined by Mike Girvin from Excel Is Fun.
This is our episode 166: Change Color Based on 1000-1999 and so on.
All right, question sent in today, Melissa from YouTube: trying to write a formula to highlight a row based on the value of a cell.
We're looking for “between 1000 and 2000”, “between 2000 and 3000”, and so on.
Alright, so the first thing I thought of, is, you know, we're going to use some Conditional Formatting, maybe, and I'm just going to put a little test formula out here, like Mike always does with his Conditional Formatting.
And I want to break these things up.
I'm guessing, you know, so the “etc.” there, right, we need to see everything between 1000 and 2000, 2000 and 3000, 3000 and 4000 and so on.
So I want to take a look at this number divided by a thousand, but I want to see which bucket they land in.
And the way I'm going to do that is to take the integer, =INT(D6/100), so that way this number will be 6,715, but the integer will just make it say “6”, which means that it's in the 6000 group.
So, you know, that little formula there is going to be part of the solution.
And I'm interested in the Min, =MIN(D6:D73), and the Max, =MAX(D6:D73), what's the range of numbers we have.
So, it looks like the Min is 1,614, I want to press Ctrl+’, which brings me the exact same formula without changing the range, F2, Home, Right, Right, change that to the MAX.
All right, so anything from one, so it looks like we're going to have about nine different ranges.
You know what, Conditional Formatting would just be a real hassle here.
So, like the theme song says, here's MrExcel with the VBA-way.
Alt, T, M, S, if you've never used macros before, change from the top setting to the second setting.
I see that this file is an xlsx, so: File, Save As and save as the exact same file name, that's no hassle, but we need to change the file type to xlsm, right there, click Save.
Now we're allowed to use macros in this file.
So, all F11, we will insert a new module and I'll just call it Sub ColorThem().
FinalRow = Cells(Rows.Count, 1).end(xlup).Row, and then: For I = 6 to FinalRow.
All right, so inside that loop we're going to say… we're going to check the value, so this value is in column D, and actually what we'll do is: ThisVal = INT(Cells(i, 4).
-that's column D- Value/1000).
So that's going to give us a number from one to nine, that's going to tell us what the color is going to be.
So, I have to assign nine different colors, but actually, I'm not going to assign nine different colors, I want to make use of the theme color trick here.
So, I'm just going to come over here, I’m going to record a quick macro that says HowToThemeColor, click OK, and the only thing I'm going to do is open this drop-down, choose one of these theme colors and choose not the top one.
But I want to apply one of the percentages, there's lighter, forty percent, and then stop recording.
All to F8 to take a look at that macro HowToThemeColor, Edit, we're going to copy all of that code, Ctrl+C, come back to our module and paste it in.
I love the macro recorder, you know, for just getting little bits of code.
So, the macro recorder does it to the selection, I'm not going to do this with the selection, we’ll say With Cells(I, 1).Resize(1, 4).Interior, Pattern is Solid, the PatternColor: we didn't have a pattern, ThemeColorsAccent5, and then right here: the TinAndShade.
This is a number that has to range from -1 to 1.
The negative numbers are very dark, the positive numbers get lighter and lighter.
So, let's say this is going to be: -ThisVal / 10.
And then PatternTinAndShade = 0.
Next I, here we go, oh, look at that, I was already thinking ahead, already putting the next “I” in.
And I think we are ready to roll.
So back here, we'll just do Alt+F8 and say ColorThem, click Run.
Ah, that is just a bit too dark.
Too, too dark.
Alright, so here, let's take that number and add maybe plus point five: + .5.
Again, the range that we have here is -1 to +1.
So this will make it run from -0.5 on up to +0.4.
I'll just run it again from here, there we go.
So now every single different band of thousands gets a different color.
And you have to be careful, you know, that you're in this this range, or otherwise you're potentially would get an error.
But at this point, it’s late in the day, 8 o’clock, and I just don't have the energy to write ten different Conditional Formatting statements, so VBA is the way to go.
Mike, let's see what you have.
Mike: Thanks, MrExcel.
Oh my heavens, look at all that beautiful Conditional Formatting for the whole row with VBA.
Now, when I read this, I didn't interpret it as: hey, you want the band for each one of the groups between the Max and the Min.
But man, if you had to do that with a formula, I'm going to go over to this sheet right here.
Man, that'd be a lot of formulas.
So I'm thinking, that we're going to have some input cells here and based on what we type here, it'll just highlight the row for that given category.
So I'm just going to give the Lower, will say 2000, and then Tab, =F4+1000, so that'll be our grouping, right?
So right now, we should only highlight the entire row, anytime it's between the Lower and the Upper.
Now, when I'm doing this, I'm going to use Conditional Formatting and formulas, and since it's for the whole row, I'd like to build the formula off here to the side, copy it down and see if I can get the patterns of TRUEs and FALSEs to work.
Then I'll copy the upper left formula from the sale and paste it into the Conditional Formatting dialog box.
Hey, there's two conditions so I'm going to use the AND function.
An AND function has to get two truths and what is it?
We're looking through this column and we're going to make sure it's between the Upper and the Lower.
Now, here's the trick: that formula is ultimately going to have to be pasted in a dialog box, that goes from this cell, all the way over, and down.
So we're always going to be looking at the Sales and we're going to have to lock the column, not the row.
So I'm going to hit the F4 one-two-three times, and we're going to ask a question.
Are you greater than, or equal to the Lower number.
=AND($D6>=$F$4); F4, that will give us the TRUE or FALSE, that's that logical one right there, comma, we have a second condition.
Same number, F4, one-two-three times to lock the column, but not the row.
In this one, I'm going to say less than the Upper: ,$D6<G4.
Now, I don't want to have: less than or equal to, because right now the 3000 is on the Upper end, but when I later type a 3000 here, this will be 4000, and I don't want to include that 3000 both, in the 2000 and 3000, and the 3000 to 4000.
So, F4 to lock that in all directions, close parentheses, Ctrl+Enter and now I'm going to copy it over just four cells, you can see, one, two, three, four, all four cells will get a FALSE, so no color.
But check this out.
By the way, I put this right next to the data set, so I can just double click and send it down.
I would never really do that, cause I like to keep my data sets with a row of empty cells all the way around, so I'm actually going to point to the edge and move this over here.
Just for safety.
Now, we can see right here, this gives us a TRUE and check it out.
Check out, it's looking for that cell right there.
It's looking at that number, even though the formula is right here, it's going to be in that cell, right there, so the whole row gets TRUEs.
Now, here's the trick: we go to the upper left corner, copy the formula, and in edit mode Ctrl+C, Esc.
Now, we highlight: Ctrl+Shift+Right arrow+Down arrow.
Now I'm going to scroll all the way up, because I actually want to see, that that's the active cell, that is the parallel Cell to that formula.
That active cell has to be the active cell, because I copied it from the upper corner.
Now, I'm going to do Alt, H, L, N. That actually is in the HOME, Conditional Formatting, New Rule… Alt, H, L, N. Arrow-arrow-arrow-arrow, Use a formula, Tab, Ctrl+V.
Now I'm going to go down and add some formatting.
Fill, whatever it is, I don't know, I'll do yellow, click OK, click OK and just like that.
I've highlighted just the rows in this band.
Now, if I type 3000 here, boom, I get a different set of bands.
5000, boom, I get a different band of rows with conditional formatting.
All right, throw it back to MrExcel.
Bill: Hey, great trick with a Conditional Formatting.
Just one rule, because you figure that she was going to randomly want one band, or the other.
It's interesting, how this really comes down to: it wasn't Dueling Excel techniques, it was Dueling trying to guess what the person wanted from their question.
Which brings up a shameless product plug here.
Over this past weekend I put together new website: excelarticles.com.
I needed to place a responsive web design to hold all of the videos, that I put up, recent Excel book and then hot topics from the MrExcel message board.
And as I was looking for these, it tended to be the items that had lots of responses.
And again, that could just mean that it was an ambiguous question that got asked.
And people out of MrExcel came up with a lot of different answers.
So check this out, a lot of interesting reading out here, works great on a phone or an iPad, or a tablet, or a PC. excelarticles.com.
Hey, I want to thank everyone for stopping by, I want to thank Mike for that great answer.
We'll see you next time for another Dueling Excel podcast from MrExcel and Excel Is Fun.
I'm Bill Jelen from MrExcel, I’ll be joined by Mike Girvin from Excel Is Fun.
This is our episode 166: Change Color Based on 1000-1999 and so on.
All right, question sent in today, Melissa from YouTube: trying to write a formula to highlight a row based on the value of a cell.
We're looking for “between 1000 and 2000”, “between 2000 and 3000”, and so on.
Alright, so the first thing I thought of, is, you know, we're going to use some Conditional Formatting, maybe, and I'm just going to put a little test formula out here, like Mike always does with his Conditional Formatting.
And I want to break these things up.
I'm guessing, you know, so the “etc.” there, right, we need to see everything between 1000 and 2000, 2000 and 3000, 3000 and 4000 and so on.
So I want to take a look at this number divided by a thousand, but I want to see which bucket they land in.
And the way I'm going to do that is to take the integer, =INT(D6/100), so that way this number will be 6,715, but the integer will just make it say “6”, which means that it's in the 6000 group.
So, you know, that little formula there is going to be part of the solution.
And I'm interested in the Min, =MIN(D6:D73), and the Max, =MAX(D6:D73), what's the range of numbers we have.
So, it looks like the Min is 1,614, I want to press Ctrl+’, which brings me the exact same formula without changing the range, F2, Home, Right, Right, change that to the MAX.
All right, so anything from one, so it looks like we're going to have about nine different ranges.
You know what, Conditional Formatting would just be a real hassle here.
So, like the theme song says, here's MrExcel with the VBA-way.
Alt, T, M, S, if you've never used macros before, change from the top setting to the second setting.
I see that this file is an xlsx, so: File, Save As and save as the exact same file name, that's no hassle, but we need to change the file type to xlsm, right there, click Save.
Now we're allowed to use macros in this file.
So, all F11, we will insert a new module and I'll just call it Sub ColorThem().
FinalRow = Cells(Rows.Count, 1).end(xlup).Row, and then: For I = 6 to FinalRow.
All right, so inside that loop we're going to say… we're going to check the value, so this value is in column D, and actually what we'll do is: ThisVal = INT(Cells(i, 4).
-that's column D- Value/1000).
So that's going to give us a number from one to nine, that's going to tell us what the color is going to be.
So, I have to assign nine different colors, but actually, I'm not going to assign nine different colors, I want to make use of the theme color trick here.
So, I'm just going to come over here, I’m going to record a quick macro that says HowToThemeColor, click OK, and the only thing I'm going to do is open this drop-down, choose one of these theme colors and choose not the top one.
But I want to apply one of the percentages, there's lighter, forty percent, and then stop recording.
All to F8 to take a look at that macro HowToThemeColor, Edit, we're going to copy all of that code, Ctrl+C, come back to our module and paste it in.
I love the macro recorder, you know, for just getting little bits of code.
So, the macro recorder does it to the selection, I'm not going to do this with the selection, we’ll say With Cells(I, 1).Resize(1, 4).Interior, Pattern is Solid, the PatternColor: we didn't have a pattern, ThemeColorsAccent5, and then right here: the TinAndShade.
This is a number that has to range from -1 to 1.
The negative numbers are very dark, the positive numbers get lighter and lighter.
So, let's say this is going to be: -ThisVal / 10.
And then PatternTinAndShade = 0.
Next I, here we go, oh, look at that, I was already thinking ahead, already putting the next “I” in.
And I think we are ready to roll.
So back here, we'll just do Alt+F8 and say ColorThem, click Run.
Ah, that is just a bit too dark.
Too, too dark.
Alright, so here, let's take that number and add maybe plus point five: + .5.
Again, the range that we have here is -1 to +1.
So this will make it run from -0.5 on up to +0.4.
I'll just run it again from here, there we go.
So now every single different band of thousands gets a different color.
And you have to be careful, you know, that you're in this this range, or otherwise you're potentially would get an error.
But at this point, it’s late in the day, 8 o’clock, and I just don't have the energy to write ten different Conditional Formatting statements, so VBA is the way to go.
Mike, let's see what you have.
Mike: Thanks, MrExcel.
Oh my heavens, look at all that beautiful Conditional Formatting for the whole row with VBA.
Now, when I read this, I didn't interpret it as: hey, you want the band for each one of the groups between the Max and the Min.
But man, if you had to do that with a formula, I'm going to go over to this sheet right here.
Man, that'd be a lot of formulas.
So I'm thinking, that we're going to have some input cells here and based on what we type here, it'll just highlight the row for that given category.
So I'm just going to give the Lower, will say 2000, and then Tab, =F4+1000, so that'll be our grouping, right?
So right now, we should only highlight the entire row, anytime it's between the Lower and the Upper.
Now, when I'm doing this, I'm going to use Conditional Formatting and formulas, and since it's for the whole row, I'd like to build the formula off here to the side, copy it down and see if I can get the patterns of TRUEs and FALSEs to work.
Then I'll copy the upper left formula from the sale and paste it into the Conditional Formatting dialog box.
Hey, there's two conditions so I'm going to use the AND function.
An AND function has to get two truths and what is it?
We're looking through this column and we're going to make sure it's between the Upper and the Lower.
Now, here's the trick: that formula is ultimately going to have to be pasted in a dialog box, that goes from this cell, all the way over, and down.
So we're always going to be looking at the Sales and we're going to have to lock the column, not the row.
So I'm going to hit the F4 one-two-three times, and we're going to ask a question.
Are you greater than, or equal to the Lower number.
=AND($D6>=$F$4); F4, that will give us the TRUE or FALSE, that's that logical one right there, comma, we have a second condition.
Same number, F4, one-two-three times to lock the column, but not the row.
In this one, I'm going to say less than the Upper: ,$D6<G4.
Now, I don't want to have: less than or equal to, because right now the 3000 is on the Upper end, but when I later type a 3000 here, this will be 4000, and I don't want to include that 3000 both, in the 2000 and 3000, and the 3000 to 4000.
So, F4 to lock that in all directions, close parentheses, Ctrl+Enter and now I'm going to copy it over just four cells, you can see, one, two, three, four, all four cells will get a FALSE, so no color.
But check this out.
By the way, I put this right next to the data set, so I can just double click and send it down.
I would never really do that, cause I like to keep my data sets with a row of empty cells all the way around, so I'm actually going to point to the edge and move this over here.
Just for safety.
Now, we can see right here, this gives us a TRUE and check it out.
Check out, it's looking for that cell right there.
It's looking at that number, even though the formula is right here, it's going to be in that cell, right there, so the whole row gets TRUEs.
Now, here's the trick: we go to the upper left corner, copy the formula, and in edit mode Ctrl+C, Esc.
Now, we highlight: Ctrl+Shift+Right arrow+Down arrow.
Now I'm going to scroll all the way up, because I actually want to see, that that's the active cell, that is the parallel Cell to that formula.
That active cell has to be the active cell, because I copied it from the upper corner.
Now, I'm going to do Alt, H, L, N. That actually is in the HOME, Conditional Formatting, New Rule… Alt, H, L, N. Arrow-arrow-arrow-arrow, Use a formula, Tab, Ctrl+V.
Now I'm going to go down and add some formatting.
Fill, whatever it is, I don't know, I'll do yellow, click OK, click OK and just like that.
I've highlighted just the rows in this band.
Now, if I type 3000 here, boom, I get a different set of bands.
5000, boom, I get a different band of rows with conditional formatting.
All right, throw it back to MrExcel.
Bill: Hey, great trick with a Conditional Formatting.
Just one rule, because you figure that she was going to randomly want one band, or the other.
It's interesting, how this really comes down to: it wasn't Dueling Excel techniques, it was Dueling trying to guess what the person wanted from their question.
Which brings up a shameless product plug here.
Over this past weekend I put together new website: excelarticles.com.
I needed to place a responsive web design to hold all of the videos, that I put up, recent Excel book and then hot topics from the MrExcel message board.
And as I was looking for these, it tended to be the items that had lots of responses.
And again, that could just mean that it was an ambiguous question that got asked.
And people out of MrExcel came up with a lot of different answers.
So check this out, a lot of interesting reading out here, works great on a phone or an iPad, or a tablet, or a PC. excelarticles.com.
Hey, I want to thank everyone for stopping by, I want to thank Mike for that great answer.
We'll see you next time for another Dueling Excel podcast from MrExcel and Excel Is Fun.
