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 1


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

  • 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.
    – zeeple
    Apr 8, 2018 at 0:43
  • 1
    @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. 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.
    – zeeple
    Apr 9, 2018 at 14:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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