Problem

Solution

CONDITIONAL FORMATTING FORMULA
=EQ(ROW(HIGHLIGHT RANGE START), QUERY({ARRAYFORMULA(ROW(INDIRECT(“1:”&ROWS(HIGHLIGHT RANGE)))), HIGHLIGHT RANGE}, “SELECT MAX(Col1) WHERE Col2 <> “ LABEL MAX(Col1) “”, 0))

Example

Check out this interactive sheet for an example implementation. Change the checkmarks in Column C to see the highlight update.

PARAMETER DESCRIPTION EXAMPLE
HIGHLIGHT RANGE The column that you’re applying conditional formatting to C2:C
HIGHLIGHT RANGE START The first cell of the above column C2

Tip: If you’re unfamiliar with conditional formatting, navigate to Format → Conditional Formatting in Sheets and reference this image for help.

Explanation

Here’s a step-by-step breakdown of the query and how the nested functions build on each other:

  1. ARRAYFORMULA(ROW(INDIRECT("1:"&ROWS(HIGHLIGHT RANGE)))) gives us a list of numbers from 1 through the total number of rows in our highlight range
  2. {ARRAYFORMULA(...), HIGHLIGHT RANGE} creates a range containing two columns
    1. The first column is the list of numbers from Step 1
    2. The second column is the values from the range that we want to highlight
  3. QUERY(...) selects the largest number from the first column where the second column isn’t blank
  4. EQ(...) returns true when it finds the row in the conditional format range that matches the result from Step 3