SOLVED

Conditional Formatting of Full Row Based on Data in One Cell Troubleshooting

Copper Contributor

Hey all!

 

I'm building a basic spreadsheet where I want the full row to be formatted based on a single cell. I can get the cell itself to format with the formula I'm using, but once I try to "Apply to" the range "=$1:$50", it fills almost every row with the conditional formatting, including rows for which the data in the chosen column doesn't fit the formula.

 

I put screenshots below

 

Any suggestions?

 

Screenshot (3).png

Screenshot (2).pngScreenshot (4).pngScreenshot (5).png

10 Replies
best response confirmed by MaxH (Copper Contributor)
Solution

@MaxH 

Change the formula to

 

=AND($E1>0, $E1<TODAY()+30)

@Hans Vogelaar 

 

When I make that change, this is what happens:

 

Screenshot (6).png

@MaxH 

Why did you use $E1>TODAY()+30 instead of $E1<TODAY()+30 ?

@Hans Vogelaar 

Sorry about that, my mistake. 

 

When I enter "=AND($E1>0, $E1<TODAY()+30)", this is what happens:

 

Screenshot (7).png

 

@MaxH 

That is what you asked for. If you want something else, please explain.

@Hans Vogelaar 

In the above photo, the row starting with the word "Shucked" is highlighted even though it doesn't fit the criteria of "=AND($E1>0, $E1<TODAY()+30)"

@MaxH 

E5 contains 14-Jan-23. That is >0 and <13-Dec-23 (TODAY()+30).

So I don't understand your remark.

@Hans Vogelaar 

It's possible that I don't understand the formula, but these screenshots are from 1-Nov-23 meaning (TODAY()+30) should equal 1-Dec-23. If this is true, the only row that should be highlighted is Row 4 because E4 contains the date 18-Nov-23 which is >0 and <1-Dec-23 (TODAY()+30). However, Row 5 is also highlighted even though E5 contains 14-Jan-23 which is not <1-Dec-23 (TODAY()+30).

@MaxH 

But 14 January 2023 is definitely before 1 December 2023. It's not 14 January 2024 !!!

@Hans Vogelaar 

Oh my gosh, I just noticed. I'm so sorry. Thank you for repeatedly trying to point that out to me and thank you for your help!

1 best response

Accepted Solutions
best response confirmed by MaxH (Copper Contributor)
Solution

@MaxH 

Change the formula to

 

=AND($E1>0, $E1<TODAY()+30)

View solution in original post