Conditional format formula to select a previous cells content (current cell -1)

Thanks to "Can Google Spreadsheets conditionally format based on a previous cells content?", I understand how to apply conditional formatting to a preceding cell, if I'm using a simple comparison, but what about something more complicated.

For example I have data in a column in the following format:

``````B4                 | C4
--------------------------------------
123 / clientname1  | 143 / clientnamea
345 / clientname2  | 143 / clientnameb
``````

With a one off formula, below, I can split and compare the two numbers:

``````=(TRIM(LEFT(\$C4,(SEARCH("/", \$C4))-1)))-(TRIM(LEFT(\$B4,(SEARCH("/", \$B4))-1))) > 0
``````

However, how would I create a reusable rule that uses something along the lines of `'current cell - minus - value one cell left'`, rather than hardcoding `\$C4, \$B4` etc each time?

The answer to the question you linked contains the answer to this one. But since it apparently didn't get through, here it is again:

In conditional formatting, you enter the formula as it should apply to the upper left corner of the formatted range. The spreadsheet will apply it to the rest of the range using relative references. So, if the upper left corner of formatted range is C4, then

• `=B4` means "the value in the cell to the left of this one".
• `=\$B4` means "the value of the cell in B column of this row"
• `=C\$1` means "the value at the top of this column"
• `=\$B\$2` means "the value in cell B2"
• Thanks @NormalHuman But how would I add that to the the formula I quoted? 'C4' seems to create an absolute comparison to C4 from all columns. '=C4' produces an error. Oct 15, 2015 at 9:11
• What is the range to which you are applying this formatting? (It's near the top of the conditional formatting dialog)
– user79865
Oct 15, 2015 at 13:05
• C3:Z3 (There's nothing in Z3, but that's the closest I can get to 'all of this row, apart from the title column - which is a another story) Oct 15, 2015 at 16:23
• Then the formula would be `=(TRIM(LEFT(C3,(SEARCH("/", C3))-1)))-(TRIM(LEFT(B3,(SEARCH("/", B3))-1))) > 0` because this is what you want for the cell C3 (upper left corner), and when applied, say, to F3, the references are remapped to `=(TRIM(LEFT(F3,(SEARCH("/", F3))-1)))-(TRIM(LEFT(E3,(SEARCH("/", E3))-1))) > 0`
– user79865
Oct 15, 2015 at 16:29