In Google Sheets how can I conditionally color a cell value if less than another cells value?

Question

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

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.