I have a spreadsheet where I have tasks, and I prioritize those tasks based on high, medium, and low levels. I currently use conditional formatting for this, so if I select from a drop-down "HIGH", the whole row will be highlight red.
However. I would like to add another condition, for the same row. I want to now click the checkbox indicating that this task is complete, and once I do, it would change to another color.

I tried using the AND formula, but I think that the conditions compete against one another, so even when I click the checkbox, the row will still stay red because of the priority condition.
What can I do?


1 Answer 1


You do not need to use AND.
You just need to create 2 rules for your range (or maybe more?) using the following formulas:

  • for the checkbox: =COUNTIF($D16, "=TRUE")
  • for the "HIGH": =$A16="HIGH"

You must also note the order these rules are applied on the attached image.

row medium

As you see you get to highlight the whole row.

If, on the other hand, want to have just the checkbox cell highlighted, you should use formulas as shown on the following image.
Can you spot the differences?

single cell

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.