Conditional formatting, cell reference shifting

Question

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?

I'm guessing that you meant to make it relative to something, right? (They are already absolute) Also how would you specify it if it were a formula on another sheet? — Martí
On the contrary, if I delete lets say rows 2 and 3, the range changes back to E2:E1000, if I paste them back, its back to E3, so they are relative. Another example, after few months working in the sheet, a simple B1:E850, now looks like this: "B1:E1,C9:E9,D13:E13,D16:D17,C17:C18,E17,C26:E28,C30:E35,B33,C37:E845,B42,B45:B845" Please keep in mind, that this is not something that Im altering, this happens to conditional formatting on its own — Konata

Erik Tyler Erik Tyler · Accepted Answer · 2021-03-09T10:08:31

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.

Conditional formatting, cell reference shifting

1 Answers