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

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

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

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

As per @Rubén's comment: "7" → 7 (text→int)
Source Link
vucalur
  • 322
  • 1
  • 3
  • 13

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 77 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"7, and, since "7"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 thea 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.)

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

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

edited tags
Link
Source Link
vucalur
  • 322
  • 1
  • 3
  • 13
Loading