This problem is similar to episode 1206, but this time the question is how to highlight the entire row of the record with the largest value. Episode 1207 shows you how. Learn Excel 97-2007 from MrExcel!
Transcript of the video:
The MrExcel podcast is brought to you by “Easy-XL”!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
OK now, in yesterday's episode, we took a look at how to use conditional formatting to highlight the lowest value in each row.
And while I showed that in a seminar to answer the person's question, someone else in seminar raise their hands and said “Wait, I have a different problem.
I need to highlight the entire row for the item that has the highest average!” So we want to look through all of these values over here in the average, and highlight the entire row.
So just like yesterday, we're going to pay attention to which cell is the active cell, the active cell here is cell H2.
We’ll create a new Conditional Formatting, New Rule, use a formula to determine which cells to format, and this time we want to look and see IF column N of the current row.
So =$N2 to the current row that says, we happen to be in row 2 right now as we're reading this formula.
But because we don't put the $ before the 2, it'll look at column N of each individual row, and see if that's =MAX($N$2:$N$14), get the whole value there.
Let's see, Format, will go with a nice green fill for the largest cell, click OK, click OK, alright.
So there, it's now highlighting the entire row of the value that's the largest.
Again, to test here, let's just put in large value straight across, 110, and Ctrl+Enter, and now that becomes the largest cell.
So a couple of different things, yesterday we talked about how to highlight the one cell in each row that was the lowest.
Here we're highlighting the entire row when this row is the largest of all the other rows.
Both of these methods using formula allows us to change the color of a cell based on other cells, probably one of the more powerful versions of formatting.
So there you have it.
Want to thank you for stopping by, we’ll see you next time for another netcast from MrExcel!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
OK now, in yesterday's episode, we took a look at how to use conditional formatting to highlight the lowest value in each row.
And while I showed that in a seminar to answer the person's question, someone else in seminar raise their hands and said “Wait, I have a different problem.
I need to highlight the entire row for the item that has the highest average!” So we want to look through all of these values over here in the average, and highlight the entire row.
So just like yesterday, we're going to pay attention to which cell is the active cell, the active cell here is cell H2.
We’ll create a new Conditional Formatting, New Rule, use a formula to determine which cells to format, and this time we want to look and see IF column N of the current row.
So =$N2 to the current row that says, we happen to be in row 2 right now as we're reading this formula.
But because we don't put the $ before the 2, it'll look at column N of each individual row, and see if that's =MAX($N$2:$N$14), get the whole value there.
Let's see, Format, will go with a nice green fill for the largest cell, click OK, click OK, alright.
So there, it's now highlighting the entire row of the value that's the largest.
Again, to test here, let's just put in large value straight across, 110, and Ctrl+Enter, and now that becomes the largest cell.
So a couple of different things, yesterday we talked about how to highlight the one cell in each row that was the lowest.
Here we're highlighting the entire row when this row is the largest of all the other rows.
Both of these methods using formula allows us to change the color of a cell based on other cells, probably one of the more powerful versions of formatting.
So there you have it.
Want to thank you for stopping by, we’ll see you next time for another netcast from MrExcel!
