AskMeAnything
Ask
Ask
28
@row_format_rania ·

I want the whole row to go red when one cell says overdue, and only that one cell changes colour — what is the rule?

A tracker with about eight columns. One of them holds a status, and I want the entire row highlighted when that status is overdue, so I can see it while scanning.

I selected the range, added a conditional formatting rule with a custom formula checking the status column, and what I get is only the status cell itself changing colour. The rest of the row stays white.

I have tried moving the dollar signs around more or less at random and got various wrong results — sometimes every row colours, sometimes none, once the wrong rows.

I would like to understand what the formula is actually being evaluated against, because I clearly have the wrong mental model and I keep hitting this.

2 answers Share
Report

2 answers

BestTopNewOldControversial

Answering anonymously — a moderator will review it first.

  • @range_starts_where · 2h ago
    @range_starts_where ·

    Here is the model, and once you have it the dollar signs stop being guesswork.

    The formula is written from the point of view of the top-left cell of the range you selected, and then copied to every other cell in that range — exactly as if you had typed it in that first cell and dragged it across and down. Each cell evaluates its own copy, and colours itself based on its own result.

    So two things follow, and they are the whole answer:

    Your range has to be the whole row block, not just the status column. If you only selected the status column, only those cells can ever colour, because those are the only cells that got a copy of the formula. This is your actual bug.

    The column reference has to be locked and the row must not be. If your data starts at A2 and status is in column E, select A2 to H500 and use a formula like =$E2="overdue".

    • $E locks the column, so every cell across the row still looks at column E rather than drifting to F, G, H as the formula is copied sideways. This is what makes the whole row respond to one cell.
    • 2 with no dollar lets the row move, so row 7's copy checks E7. This is what makes each row independent.

    All four of your wrong results are one of those two being backwards, and now you can predict which.

    30
    Share
    Reply

    Answering anonymously — a moderator will review it first.

    Report
  • @dollar_sign_dan · 2h ago
    @dollar_sign_dan ·

    The rule in one line, which I wrote on a note years ago and still use: lock what must not move, leave free what must follow.

    Worked through your case, imagining the formula being copied:

    • Copied right from A2 to B2, C2 and so on. Column must not move, so $E.
    • Copied down from A2 to A3, A4. Row must follow, so 2 plain.

    Hence $E2. Both halves are decisions, not conventions.

    The other three combinations, so you can recognise what you saw:

    • E2 — nothing locked. As it copies right it looks at F, G, H. You get colour appearing in unrelated columns and it looks random.
    • $E$2 — both locked. Every cell in the range checks the same single cell, so either everything colours or nothing does. That is your all-or-nothing result.
    • E$2 — row locked, column free. Every row checks row 2, so the whole block matches row 2's status.

    The most common practical mistake after those is the range not starting where you think. If your range starts at A2 the formula is written for row 2; if you selected from A1 including the header, write it for row 1 or everything is off by one. That is the "once the wrong rows" case.

    25
    Share
    Reply

    Answering anonymously — a moderator will review it first.

    Report