I have looked at a bunch of forums and still can not find a way to change the color of the current cell if it has a lower value than the cell above. I currently have =INDIRECT(ADDRESS(ROW()-1,COLUMN())) as my formula. With this, I also have the "format cells if" set to "less than".

It looks something like this:

Any help on this would be greatly appreciated. Thank you.

CodePudding user response：

Change range in apply to range to B4:H184 and formula from =INDIRECT(... to =B3

CodePudding user response：

Here's my test interface. Cells B7:G7 have the numbers 1-6, and cells B8:G8 have conditional formatting. We apply the conditional formatting as follows:

What's happening here is that the conditional formatting is explicit for cell B8, then the remaining cells get inferred from the given formula. It's not a great solution since it relies on your intuition of how the formula is going to be extended, but it works.

Since we're using references, we can even extend it in two directions:

To generalize, if you can create a formula in a cell that returns TRUE or FALSE, that formula can be used by the Custom formula section of the conditional formatting interface. If your formula refers to non-absolute singleton values such as B7 then applying the formula to a range of cells will treat those values as relative references in exactly the same way that it would if you pasted the formula into the cell itself.