1

Conditional formatting, even when applied to the whole row/column, treats each cell individually and applies formatting only to the cells that meet criteria.

Example:
An attempt to highlight whole row 7 if any of the cells is not blank:

enter image description here

Result: As mentioned above, highlights only specific cells, not the whole row.

If only there was a range-aware version of the isblank() function, that would operate on a range of cells instead of treating each cell individually. Like so: =not(isblank(row(A7:7))) - This works not as desired, i.e. it evaluates row(A7:7) as an integer literal 7, and, since 7 is not blank, the whole expression evaluates to TRUE, regardless of the contents of the cells.

Is it possible to achieve this range-aware formatting on formula level, i.e. without a custom script?

(Yeah, yeah, I'm aware that a custom script for that is not that time-consuming to write, but more people might be facing the same problem, so a more user-friendly solution is welcome.)


Update 6/14/2018:

Formatting in google sheets seems to be buggy and/or poor-documented. That might be the reason why @Ceu Melo's solution works for me even with numbers, despite @Rubén claiming otherwise:

enter image description here

Bugginess demonstration:

An attempt to highlight whole row if a condition holds true for a specific cell (B2) in that row:

enter image description here

No better if B2 is changed to C2 in the condition:

enter image description here

On a real-world document it looks equally terrible:

enter image description here

Changing data format didn't seem to affect formatting behaviour at all.
In the screenshots it's set to "Automatic".

  • Don't enclose integer literal between quotes as Google Sheets and other spreadsheets handle them as text. – Rubén Mar 1 '18 at 1:43
  • @Rubén fixed. Is it ok now? – vucalur Jun 12 '18 at 10:39
  • If found the edit made today confusing, after the hr it says that one answer works for you but that answer was not currently the accepted answer, later the edit include a "bugginess demonstration", but it's not refers previously referred answer. What is the point of that demonstration in the context of a Q&A thread? – Rubén Jun 14 '18 at 22:43
  • I wanted to see your response before accepting. – vucalur Jun 15 '18 at 8:25
2

Try the function COUNTA, it counts the number of cells in a range that are not empty,

and indicate more than zero for conditional rule:

enter image description here

Was this what you were looking for?

  • That will not work for cells having numbers – Rubén Mar 1 '18 at 1:42
  • @Rubén Take a look at "Update 6/14/2018" section in the question. – vucalur Jun 14 '18 at 21:22
  • When I posted my comment COUNTA didn't work for cells having numbers but today it works. – Rubén Jun 14 '18 at 22:44
2

For area A1:E500 using a custom forumula:

=if($B1="Purchased",1)

Will highlight the entire row if Purchased is in the B column. The key point is locking the check to the B column with the $.

1

Is it possible to achieve this range-aware formatting on formula level, i.e. without a custom script?

Short answer

Yes, it's possible.

Instructions

For the "Apply to range" setting use

7:7

Considering that

  • 0 values are parsed as FALSE
  • Other numbers are parsed as TRUE

for the "Custom formula" setting use

=COUNT(7:7)+COUNTA(7:7)
  • COUNT counts cells having numbers
  • COUNTA counts cells having texts, even ""

Or

=COLUMNS(7:7)-COUNTBLANK(7:7)
  • COLUMNS return the number of columns
  • COUNTBLANK return the number of blanks


NOTES

June 14, 2018

After a comment from the OP and reading today's question update, I tested the solution proposed by Ceu Melo and today it worked for numbers too, despite that COUNTA is supposed to count only cells that have text, not numbers.

-1

=counta(7:7)>0

Worked for me in conditional formatting for a range of 30 rows x 3 columns.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.