Forum Discussion
How to make whole rows highlighted by date and text?
I'm trying to stay on top of the invoices that I place in my spreadsheet so that whole rows are automatically highlighted once 14 days has passed if they haven't been entered into my accounting software.
I typically just change a columns text from "Un-entered" to "Entered"
Currently the formula that I came up with that highlights dates that are 14 days and older is
=($C2<TODAY()-14)*($C2>1) but my two issues are
1) How do I condition it so it highlights the whole row and not just that single column?
2) How do I make it so the only highlighted rows are the ones that are:
* Older than 14 days
&
* Don't have the word "Entered" in a separate column
2 Replies
First, delete the existing conditional formatting rule for column C.
Select all the rows that you want to format, not just column C.
Let's say that row 2 is the top row in the selection, and that the active cell in the selection is in row 2.
On the Home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula
=AND($C2<TODAY()-14,$C2<>"",$K2<>"Entered")
Here, K is the column in which you type "Entered".
Click Format...
Specify the desired formatting, then OK your way out.
Lenescar_B Select the entire range to which the formatting should should be applied. Not just the one column.
