Conditional formatting based on adjacent cell value even when the first adjacent cell in row is blan-CodePudding

I would like to colour cell C3 red, as the value is less than the next filled cell on row 3 (E3).

CodePudding user response：

You need custom formula in the formatting rules for range C2:W.

And you can use the formula below for green:

=and(not(isblank(C2)),C2>index(filter(D2:$W2,arrayformula(not(isblank(D2:$W2))))),1,1)

And make another conditional formatting rule with the same range and change > accordingly for red.

Note that the equal case does not have formatting in your example.

To understand why, there are a few components at work. I'll give an outline in case OP or any passer-by wants one.

First is how conditional formatting in Google Sheet works. I will be brief here. The range we put in the rule is C2:W. Google Sheet will use the beginning cell in that range as a reference. For C2:W, the 1st cell is C2. In the formula, the cells are read in terms of relative position to that reference cell. So when evaluating formatting for cell C2, it treats the formula as is. But, for example, when evaluating formatting for cell C3, Google Sheet iterates all non-fixed ranges by 1 row. Another example: for D3, all non-fixed ranges are iterated by 1 row and 1 column.

Whenever the formula evaluates to true, the format will be applied -- although that is subject to further formatting if you have subsequent formatting rules which apply to the given cell.

Next are the components of the conditional formula.

not(isblank(C2)) checks for blank cells and makes the whole formula only true when the cell is non-blank.

For filter() usage, please consult official documentation. I will explain how filter() is applied to your example.

In our use, we are trying to rid of empty cells in the range that is on the same row as the cell in question and goes from the next column to column W. (I am using column W because there is no known end column in your image. Please adjust to your application accordingly.) Same row as C2 means row 2. Hence the digit 2 in D2:$W2. No $ sign for row because row index is meant to iterate through our range C2:W in the formatting rule. No $ sign for D because we mean 1 column next to the cell in question and the exact column index should iterate. We fix column W because the end column does not evolve and is thus not meant to iterate. Neglecting this will not particularly change the result in your example. However, it is good to be clear with the meanings.

arrayformula(not(isblank(D2:$W2))) produces a local row array whose cells are true/false that each represents whether the corresponding cell is non-blank. filter() in turn only keeps cells from D2:$W2 for cells in arrayformula(not(isblank(D2:$W2))) that are true.

We only care about the 1st non-blank cell. To retrieve the 1st cell from a (local) array, we use index(...,1,1). (That said, omitting index() also happens to work for the 1st cell in particular because when comparing a single cell with > or < to an array range, only the 1st cell of the array is used.)

CodePudding user response：

Apply a conditional formatting to the range desired (starting from cell C2) using this formula:

=IF(C2="",FALSE,OFFSET(C2,0,AGGREGATE(15,6,(COLUMN(D2:W2)-COLUMN(D2) 1)/(D2:W2<>""),1))>C2)