Conditional formatting: adding rows adds additional conditions; how to avoid that?

Hello!

I am attaching the file and two screen shots, otherwise it will be hard to explain.

In the tab called TASKS I have a set of conditional formatting rules, which initially extend from row 12 till row 42 (different columns for different rules).

I hoped that adding a new row would extend the conditional formatting row range to 12 - 43, because one more row is added. It turns out not to be true.

For example, if I want to insert a row between tasks 1.9 and 1.10, I do the following (in each case the formatting result is the same):

1) copy 1.10
click Insert copied cells
get 1.10 on row 22 now
the number of tasks is now 12 with the last one 1.12 (initially the last one was 1.11)

2) add a new row between 1.9 and 1.10
copy entire 1.9 (or 1.10) row
paste copied row into a newly created one

In both cases the result is the same. New conditional formatting rules are added (for the row 22), and the range of application of all initial rules is also changed - it is "split" now into a few ranges (see screen shot).

How can I add rows and only extend the existing condition rules, without splitting the initial range and without getting new conditional rules?

Thank you very much!

I have found the brilliant template created by Vertex42 - a masterpiece, at least to me, who is just learning. )
In it, whenever you add any row to any part of any sheet of the workbook, the conditional formatting rules' ranges neatly increment by the amount of rows you added, and do not create new rules for any added row (the problem I have in my file).
How did they achieve that?

Originally Posted by JeteMc

Perhaps using the following formula for the 'Refers to:', as prescribed in this article, would work better:

Formula:

=OFFSET(TASKS!$F$12,0,0,COUNTA(TASKS!$A$12:$A$201),1)

Let us know if you have any questions.

*Thank you very much for your suggestion. I decided to try at least on one rule and one column.
I picked the rules for column N -> cells there should turn blue and values turn white, if the cell value = -1.
In the applied to section I tried to use =OFFSET(TASKS!$N$12,0,0,COUNTA($N$12:$N$1000),1)
When I clicked OK, nothing happened in the file, the values of cells = -1 didn't change their appearance.
When I went back to Conditional formatting, the range in applied to was back to the initial one.

I also tried with the defined name. Same result.

If by any chance you have a bit of time to look at the vertex's file I attached previously, I would be grateful if you can explain how they achieved such formatting that:
- no new rules are added, if you add rows, namely the new rules, corresponding to a newly added row, appears;
- but rather the range's last cell is extended by the number of added rows.

I don't understand what is incorrect in the way I have written rules, and how they have achieved such wonderful and correct formatting.
Thank you very much!

I deleted a few sheets from the example file I found, and left only two sheets. Also I have changed the rules by deleting any that depend on other sheets. Now there are only 2 sheets: Accounts, Transactions.

In Transactions sheet I added two new rows 24 and 25, without copying any formatting and formulas. And the Applied to range has correctly changed to account for two additional rows, thus it was rows 5:49, and became rows 5:51. No additional rules were added. So it is obvious that I do something truly wrong in my file, as I can't get the same correct outcome, but do get new rules added as I add new rows.

I am truly sorry that it takes me so long to get back. I have been trying everything, and no, somehow it doesn't work, including using the OFFSET function in Applies To (or a defined name with the OFFSET function). 'Applies to' doesn't allow any other formatting of ranges except for usual highlighted ones.

In a continuing effort to find the problem in my files, I have created two files once again (formatting_quns is the initial one; formatting_quns_2 is the changed one). And here is what I did and what happens as a result.
The initial range was from row 12 to row 41.
Screen Shot 2017-08-21 at 5.29.55 PM.png

1) highlighted rows 19 - 20
2) clicked Insert, and inserted 2 rows; now new 19 and 20 empty rows are added
3) copied row 18
4) inserted the copied row into both new row 19 and 20

All these steps changed the initial range by breaking it down into 2 blocks: first block from row 12 till row 18, and second from row 21 to row 43.
And added new conditional rules for inserted rows 19 and 20.
Screen Shot 2017-08-21 at 5.33.44 PM.png

Screen Shot 2017-08-21 at 5.34.41 PM.png

This happens constantly.
Using offset function sounds truly helpful, but Applied to doesn't allow that.

It seems that I am doing something wrong, and/or miss some basic understanding. These issues do not occur in the vertex's file I showed as an example of perfectly working conditional formatting.