Skip to main content
4 of 4
Added a note demonstrating formatting bugginess
vucalur
  • 322
  • 1
  • 3
  • 13

Apply formatting to the WHOLE row/column if ANY of the cells fulfils condition

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

vucalur
  • 322
  • 1
  • 3
  • 13