Google-sheets – Using conditional formatting to highlight entire area instead of just one cell

In Conditional Formatting, select Custom Formula is and use the formula:

=AND(ISBLANK(B4),ISBLANK(B5))

Then when both B4 and B5 are empty, they will both be coloured; when either one has text in it, they will both be white.

Both data validation and conditional formatting can reference the values in other cells.

For example, if I require all values in column A to be greater than the value in cell C1, I would use Data Validation with custom formula =A1>C$1. The logic is as follows:

• Validation rules (and conditional formatting rules) are entered as they should apply to the upper left corner of the range. From there the reference will be automatically mapped if needed: e.g., the rule =A1>C1 would compare A2 to C2, and so on.

• The formula =X>Y returns True if X is greater than Y and False otherwise. Similarly, one can use expressions like =X>=Y or =X=Y. The initial = is just the symbol indicating we have a formula to evaluate.

With conditional formatting, you can use the same custom formula as above. Or, pick "greater than" from the list of criteria and enter =C$1 as the value to compare to. Not C$1, because C$1 is simply text "letter C, dollar sign, digit 1".