# Conditional Formatting in Google Sheets based on multiple comparisons

I am trying to change the color of a cell using conditional formatting in Google Sheets using the following formula:

``````=IF(B4<=B1, (IF(C4<=C1, (IF(D4<=D1, (IF(E4<=E1, ))) ))))
``````

Pseudo code would be long the lines of:

``````IF B4 is less than or equal to B1,
AND C4 is less than or equal to C1,
AND D4 is less than or equal to D1,
AND E4 is less than or equal to E1,
THEN change the color of the cell
``````

However, this does not seem to be working. Any pointers?

## 1 Answer

Conditional formatting formulas should return TRUE or FALSE.

NOTE: 0 is coerced as FALSE while other number values as TRUE

For the rule that is expressed in the question you could use AND

``````=AND(B4<=B1,C4<=C1,D4<=D1,E4<=E1)
``````
• Perfect. The one thing I would add to this question is how to make it so that the formula can auto increment as you pull the handle down on several cells. For example, I change the formula to this: =AND(B4<=B\$1,C4<=C\$1,D4<=D\$1,E4<=E\$1) so that the cells I was comparing against would not increment and the others would. However, when I created the conditional formatting, I couldn't see how to have the formula update as I either dragged the handle down, or simply selected a bigger range in the conditional formatting right-hand pane. Apr 8, 2018 at 0:43
• @zeeple Simple select a bigger range in the conditional formatting right-hand pane. The formula should be written for the top left cell in the range, Google Sheets will internally manage the references for the the rest of the range cells. If this isn't clear, please post a new question.
– Rubén
Apr 8, 2018 at 1:59
• thanks for the comment. I feel like I tried that option and it didn't work. I'll give it another try though. Apr 9, 2018 at 14:13