The Sheet I am working with has a multiple columns, each with a range of integer values. I would like to check to see if that integer value is less than another cells integer value, and if so conditionally color it.

I've gotten the conditional formatting working, but it's coloring cells that shouldn't be colored. Any thoughts?

Google Sheet: https://docs.google.com/spreadsheets/d/1kr3uM_W5PjTD2BCBFufl7OR1Zqm2x8oP7uEPM-zHN5Q/edit#gid=0

Conditional Comparison and Coloring is broken


1 Answer 1


When you use a relative reference, such as B17, to format a range, it will be used in a relative way. That is:

  • the top cell of the range, B2, gets compared to B17
  • the cell below it, B3, gets compared to B18.
  • B4 gets compared to B19, and so on... B7, highlighted on your example, is compared to B22 which is hidden on the sheet, but presumably B7 < B22 holds true.

If the goal is to compare all cells in the range to B17, use the absolute reference B$17 as pnuts suggested.

  • Thank you zaq, this was exactly what was needed. Much appreciated.. Commented Feb 2, 2017 at 5:51

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.