0

I'm a Google sheet newbie. I'm trying to work out how to set a conditional formatting 'greater than' rule for a cell block where the criteria value is outside the block. I've seen elsewhere that the solution requires a custom rule but I can't understand how to build the equation.

The cell block is: H10: M64. The criteria value is at cell 'I6'

What equation do I set to force the conditional formatting to apply at a cell level within the H10:M64 block, so that if the cell value is greater than the value at 'I6' the conditional formatting will apply?

user2868835
  • 1,250
  • 3
  • 19
  • 33
  • you should defenitely have look at this : https://stackoverflow.com/questions/20545961/google-spreadsheet-conditional-formatting-based-on-another-cell-value – rabsom Sep 13 '17 at 18:28

1 Answers1

2

The key to this is absolute vs. relative cell references.

A primer on absolute vs. relative cell references

If you write a formula in cell A3 that says =A1*2, that reference is relative. If you copy your formula into cell B3, it now says =B1*2.

You can keep this from happening and always refer to the exact same cell, no matter where you copy the same formula, with absolute cell references. Add $ before the row and column references to lock the formula in on the same cell reference. If your original formula says =$A$1*2, then when you copy it over to cell B3 then the formula stays exactly the same: =$A$1*2.

You can also just freeze either the row or the column, referencing $A1 (So the row will shift relatively, but will always refer to column A) or A$1 (the columns will shift relatively, but will always refer to row 1).

How this relates

In your conditional formatting rule, you want every cell in the range to be compared to the exact same cell: $I$6. But how do you get every separate cell in the range H10:M64 to compare itself to $I$6? Relative cell references.

Write your custom formula like so:
=H10>$I$6

By comparing the relative cell reference for the top-left cell(H10) in the range to the absolute cell reference for $I$6, The formula will compare every cell relative to H10 in the range to the same comparison cell and format accordingly:
H10>$I$6,
H11>$I$6,
...,
M64>$I$6

Screenshot:
screenshot

moriah
  • 323
  • 4
  • 16