How to Highlight a Row Based on a Cell in Google Sheets

By Tomosuke Hasegawa · Updated Oct 29, 2026 · Intermediate · 6 min read · includes 2 free exercises

In short: Select all the columns you want colored, open Format > Conditional formatting, choose Custom formula is and lock the column with a dollar sign, such as =$B2="Done". The whole row changes color when column B says Done.

The steps

  1. Select the cells you want to format.
  2. Click Format, then Conditional formatting. A toolbar opens on the right.
  3. Under "Format cells if", choose Custom formula is.
  4. Enter your formula in the Value or formula box and choose the formatting.
  5. Click Done.

The example

Here is a task list in cells A1:C5. You want a whole row colored when its Status is Done.

ABC
1TaskStatusHours
2ReportDone6
3SlidesOpen3
4AuditDone8
5InvoiceOpen2

Color the whole row

Select A2:C5, which covers every column of the rows. Write the formula for the first row of the selection:

=$B2="Done"

This colors the Report and Audit rows. Here is why: the dollar sign before B locks the check to column B, so cells in columns A, B and C all look at the Status in their own row. The row number has no dollar sign, so it moves down with each row. Google's help page gives the same idea with =$B1="Yes" over columns A to E.

Use a number test

The condition can be any comparison. To color rows where Hours is above 5:

=$C2>5

This colors the Report (6) and Audit (8) rows.

Rules run in order

Per Google's page, rules run in the order listed and the first one that is true sets the format. Drag the rules to reorder them if two rules could apply to the same row.

Common mistakes

Try it yourself

Type your formula into the box, starting with =, then press Check. This is the formula you would enter in the conditional formatting panel.

Exercise 1: Rows marked Done

Write the custom formula that colors the whole row when the Status in column B is Done. The rule is written for row 2.

Hint: lock the column but not the row.

Exercise 2: More than 5 hours

Write the custom formula that colors the whole row when the Hours in column C are greater than 5. The rule is written for row 2.

Hint: lock column C with a dollar sign.

What to learn next

Syntax and rules in this guide were checked against Google's Highlight a row based on a cell help page. See all guides.