CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formulaformulaconditional-formatting
Konata
Konata

Reputation: 275

Conditional formatting, cell reference shifting

I'm using Google Sheets to track tasks, each task is a single row, which can have a certain "Status" in of the cells.

I'm using conditional formatting, to highlight when a "Task" has a certain "Status"

Example:

Apply to range: "E2:E1000" Format rules: "If text contain "Completed" Highlight that cell.

The issues that I'm encountering on daily basis: Due to adding/deleting/sorting these rows, my conditional formatting range shifts. So the range of the example above currently shifted to "E4:E980".

Is there a way to imitate "indirect" function in conditional formatting, or any other way to make formatting range stay absolute?

Upvotes: 0

Views: 611

Answers (1)

Erik Tyler
Erik Tyler

Reputation: 9355

Change the "Apply to" range from E2:E1000 to E:E. It will self adjust to say E1:E1000 (or to fill the entire column range, whatever number of rows you have); but it should hold up against insertions or deletions of rows.

Upvotes: 2

Related Questions