CODEWITHSUNDEEP
excelgoogle-sheets
jds
jds

Reputation: 8269

Conditional formatting based on previous cells while skipping empty cells

I want conditional formatting such that a cell is dark green if it is a "1" and the next 3 non-empty cells in higher rows (lower on the page) are also "1". For example, everything in this image is correct except the top right "1" should be dark green:

enter image description here

The left most column is straightforward. The top cell in the middle column is not dark green because the streak of 1's was broken by a 0. And the right most column is incorrect as I mentioned above.

Here is my current conditional formatting:

=AND(
  EQ(INDIRECT(ADDRESS(ROW(), COLUMN())), INDIRECT(ADDRESS(ROW()+1, COLUMN()))),
  EQ(INDIRECT(ADDRESS(ROW(), COLUMN())), INDIRECT(ADDRESS(ROW()+2, COLUMN()))),
  EQ(INDIRECT(ADDRESS(ROW(), COLUMN())), INDIRECT(ADDRESS(ROW()+3, COLUMN())))
)

But this really just checks that the current cell matches the three lower cells. How can I skip over an arbitrary number of empty cells?

Upvotes: 0

Views: 49

Answers (1)

Tom Sharpe
Tom Sharpe

Reputation: 34245

It can be done by repeated use of offset:

(1) to obtain the minimum range which contains three non-blank cells

(2) to check if this range contains three 1's:

=AND(A1=1,COUNTIF(OFFSET(A2,0,0,MATCH(3,COUNTIF(OFFSET(A2,0,0,ROW($1:$10)),"<>"),0)),1)=3)

enter image description here

Note that you need to choose how many cells to look ahead (here set to 10). You could look ahead all the way to the bottom of the range that you are formatting, but might encounter performance issues eventually.

Upvotes: 1

Related Questions