SOLVED

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

Copper Contributor

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

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?

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

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

Change the formula to

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

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

When I make that change, this is what happens:

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

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

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

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

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

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

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

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)"

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

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

So I don't understand your remark.

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

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).

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

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

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

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!