Amal wants to highlight an entire record in red when the date falls within two weeks and then, leave the 'Done' column not marked as True. In Episode #1441, Bill shows us the tricky Conditional Formatting method to get the results we want.
...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!
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
...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!
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
Transcript of the video:
Mr Excel Podcast is sponsored by Easy-XL.
Learn Excel from MrExcel, Episode 1441 – Conditional Format The Entire Row Hey, today's question is sent in by Amal.
Amal has data set, and he has a couple things that he wants to check for.
He wants to see if the status that is not TRUE, or X, or Y, or something, and also if the due date is within 14 days today.
So I decided I was going to try and puzzle this out before I went into the conditional formatting.
=AND(NOT(D2=True).
Equal, AND.
First thing, I want to know the NOT function, that this is equal to True.
Now if it was just True/False, it would be simple enough, but you know, he might be using in parentheses, a Y, or whatever he's using to market, =AND(NOT(D2="Y").
In my case, I use True to market.
So we want that to be True.
Also, we want the date, so that’s E2, to be less than, in parentheses, TODAY, plus 14 or maybe 13, depending on your counting, how you count two weeks, =AND(NOT(D2=True),E2<(TODAY()+14)).
And that closes the AND.
We'll copy that down, and you'll see that just these two items, which are due soon are marked as True.
Now I need to go back and edit this formula, and add some dollar signs in.
=AND(NOT($D2=True),$E2<(TODAY()+14)).
I always need to point to column D, and I always needed to point to column E because in essence, what I'm going to be doing, it's like I'm going to be taking that formula, and copying it on top of this whole range.
So I need to always lock that down to column D, but the row needs to be able to change – 2, 3, 4, 5.
So I'm going to copy that to the clipboard, Ctrl C. I'm going to choose starting here in cell A2, the whole data set, and then Alt+O+D for conditional formatting, new rule.
I want to use a formula.
That's the only one that's going to work here, and just paste, format, say you wanted it highlighted in red.
So we'll use a nice red color there.
Click OK.
Click OK.
And Click OK and check that out.
It formats the whole record in the database.
The formula is, we’re using that formula.
It’s really the only way to format a cell looking at some other cell in that row.
And again, it's easier to build it out here first, and get it working, and then go into conditional formatting.
Al right.
Well hey, I wanted to thank Amal for sending that question in.
And I want to thank you for stopping by.
I’ll see you next time for another netcast from MrExcel.
Learn Excel from MrExcel, Episode 1441 – Conditional Format The Entire Row Hey, today's question is sent in by Amal.
Amal has data set, and he has a couple things that he wants to check for.
He wants to see if the status that is not TRUE, or X, or Y, or something, and also if the due date is within 14 days today.
So I decided I was going to try and puzzle this out before I went into the conditional formatting.
=AND(NOT(D2=True).
Equal, AND.
First thing, I want to know the NOT function, that this is equal to True.
Now if it was just True/False, it would be simple enough, but you know, he might be using in parentheses, a Y, or whatever he's using to market, =AND(NOT(D2="Y").
In my case, I use True to market.
So we want that to be True.
Also, we want the date, so that’s E2, to be less than, in parentheses, TODAY, plus 14 or maybe 13, depending on your counting, how you count two weeks, =AND(NOT(D2=True),E2<(TODAY()+14)).
And that closes the AND.
We'll copy that down, and you'll see that just these two items, which are due soon are marked as True.
Now I need to go back and edit this formula, and add some dollar signs in.
=AND(NOT($D2=True),$E2<(TODAY()+14)).
I always need to point to column D, and I always needed to point to column E because in essence, what I'm going to be doing, it's like I'm going to be taking that formula, and copying it on top of this whole range.
So I need to always lock that down to column D, but the row needs to be able to change – 2, 3, 4, 5.
So I'm going to copy that to the clipboard, Ctrl C. I'm going to choose starting here in cell A2, the whole data set, and then Alt+O+D for conditional formatting, new rule.
I want to use a formula.
That's the only one that's going to work here, and just paste, format, say you wanted it highlighted in red.
So we'll use a nice red color there.
Click OK.
Click OK.
And Click OK and check that out.
It formats the whole record in the database.
The formula is, we’re using that formula.
It’s really the only way to format a cell looking at some other cell in that row.
And again, it's easier to build it out here first, and get it working, and then go into conditional formatting.
Al right.
Well hey, I wanted to thank Amal for sending that question in.
And I want to thank you for stopping by.
I’ll see you next time for another netcast from MrExcel.
