I'm working on a spreadsheet with a lot of conditional formatting.

But I've got one thing that I'd like to change but isn't working as wanted.

I'd like to highlight an individual row ( lets say Row 9) if the cell J9 doesn't contain the word "PUBLIC".

Now I used a formula to determine which cell to use the conditional formatting on. I've used the formula =$J$9<>"PUBLIC"

The problem is that we have to insert rows above sometimes and with that the conditional formatting moves down. E.g. if I insert a row above, the formula changes to =$J$10<>"PUBLIC"

How is it possible to keep the formatting always on Row 9?

Thank you so much for your help

  • 1
    Conditional formatting is a property of a cell, not of a cell address. Inserting moves it ALWAYS. How is it possible to keep the formatting always on Row 9? Detect inserting, and when, then delete wrong moved CF and set new correct one. – Akina Jul 26 at 6:06

As Akina says, when you insert or delete rows, references (absolute OR relative) in the formulas for conditions change, just as if they were Named Ranges. So the cell stays the same. (Now that's absolute!!) Referring to that as the formatting is a cell's property seems to be a very perceptive way to refer to this.

However, you can use INDIRECT() in a conditional formatting rule formula, the same as you can in sheetside formulas. So insted of $J$9, use INDIRECT("$J$9") and insertions and deletions will no longer change the cell you wish to test for applying, or not applying, your conditional format.

(VERY much like we use INDIRET() in sheetside formulas for the VERY same reason involving insertions and deletions.)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.