My rule only colors the status cell, not the whole row — what is wrong?

The formula is missing the $ anchor on the column. Change `=C2="Done"` to `=$C2="Done"`. Without it, every cell evaluates its own column rather than column C, so only column C cells ever match.

How do I color rows based on partial text, like any status containing 'Block'?

Use `=SEARCH("Block",$C2)>0` as the formula. `SEARCH` is case-insensitive and returns the position of the match; the `>0` check makes it a boolean. Keep the `$C` anchor.

The script adds duplicate rules every time I run it — how do I fix that?

Fetch the rules first, check whether a rule for that formula already exists before pushing, or clear only your managed rules. The simplest fix for a small sheet: call `sheet.clearConditionalFormatRules()` at the top of the function and rebuild from scratch each run, accepting that manually added rules will be lost.

Can I color rows based on a dropdown value set with data validation?

Yes. The formula approach is identical — `=$C2="Done"` works regardless of whether the value was typed or chosen from a dropdown. The validation and the conditional format are independent; they both just read the cell value.

// Sheets · Apps Script

Color a row based on a cell value in Google Sheets.

Use a custom-formula conditional formatting rule or Apps Script to highlight an entire row when a cell matches a value — with the $ anchor that makes the difference.

“I want to highlight an entire row automatically when a status cell says "Done" (or any other value), but my conditional formatting rule keeps coloring only one cell instead of the whole row.”

The script

copy · paste · trigger

colorRowsByStatus.gs

Apps Script

// Color rows in column A:E based on status in column C
// Run once to apply; re-run after adding new status values
function colorRowsByStatus() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var range = sheet.getRange('A2:E');
  var rules = sheet.getConditionalFormatRules();

  var doneRule = SpreadsheetApp.newConditionalFormatRule()
    .whenFormulaSatisfied('=$C2="Done"')
    .setBackground('#d9ead3')
    .setRanges([range])
    .build();

  var blockedRule = SpreadsheetApp.newConditionalFormatRule()
    .whenFormulaSatisfied('=$C2="Blocked"')
    .setBackground('#f4cccc')
    .setRanges([range])
    .build();

  rules.push(doneRule);
  rules.push(blockedRule);
  sheet.setConditionalFormatRules(rules);
}

Generate yours

Need a variant? Gnaw writes a custom version from one sentence — fields, triggers, edge cases handled.

Walkthrough

Why the entire row won't highlight (the $ anchor problem)

When you write a custom-formula rule in the Sheets UI and type `=C2="Done"` without a dollar sign, Sheets evaluates each cell relative to itself. Cell D3 sees `=D3="Done"`, not `=C3="Done"`. The row gets highlighted only in column C, because that is the only cell where the relative reference happens to point at C.

Fix it by locking the column with a dollar sign: `=$C2="Done"`. The column is now absolute (always C), the row is still relative (shifts down for each row), so every cell in the row evaluates against the same status column. Leave the row number unlocked on purpose — that is what lets the rule walk down the sheet.

The first time I hit this, I spent twenty minutes wondering why the formatting fired on half the cells. The `$` placement is the whole story.

Setting the rule range correctly

The range you apply the rule to controls how many columns get colored. `A2:E` covers five columns starting at row 2. If your sheet has headers in row 1, starting at row 2 keeps them unaffected. If your data grows past column E, extend the range or use an open-ended range like `A2:E1000`.

One common mistake: applying the range as `C2:C` (just the status column) and then wondering why only that column changes color. The range and the formula work together — the formula decides which rows fire, the range decides which cells get painted.

In the UI: Format → Conditional formatting → Apply to range. In the script: `.setRanges([range])` where `range` is the result of `sheet.getRange('A2:E')`.

Adding rules via script without wiping existing ones

The script above calls `sheet.getConditionalFormatRules()` first, pushes the new rules onto the existing array, then writes the whole array back with `sheet.setConditionalFormatRules(rules)`. That pattern preserves any rules you already set by hand.

If you call `sheet.setConditionalFormatRules([doneRule, blockedRule])` directly — without fetching first — you will silently delete every other rule on the sheet. I keep a comment in the function reminding me to fetch-before-set; it is an easy one to get wrong on a copy-paste.

Run the function once from the Apps Script editor (Extensions → Apps Script → Run). It does not need a trigger unless you want the colors to update automatically as new rows are added; for that, attach it to the `onEdit` trigger via the Triggers panel.

Want a custom version?

Describe your sheet and the rule you want. Gnaw writes the Apps Script — fields, triggers, edge cases — in one shot.

Generate yours Browse BullPack

FAQ

4 questions

My rule only colors the status cell, not the whole row — what is wrong?: The formula is missing the $ anchor on the column. Change `=C2="Done"` to `=$C2="Done"`. Without it, every cell evaluates its own column rather than column C, so only column C cells ever match.
How do I color rows based on partial text, like any status containing 'Block'?: Use `=SEARCH("Block",$C2)>0` as the formula. `SEARCH` is case-insensitive and returns the position of the match; the `>0` check makes it a boolean. Keep the `$C` anchor.
The script adds duplicate rules every time I run it — how do I fix that?: Fetch the rules first, check whether a rule for that formula already exists before pushing, or clear only your managed rules. The simplest fix for a small sheet: call `sheet.clearConditionalFormatRules()` at the top of the function and rebuild from scratch each run, accepting that manually added rules will be lost.
Can I color rows based on a dropdown value set with data validation?: Yes. The formula approach is identical — `=$C2="Done"` works regardless of whether the value was typed or chosen from a dropdown. The validation and the conditional format are independent; they both just read the cell value.

// one good script a week