Why do some (but not all) Conditional Formatting “

问题:

A quick look around the Internet and you can easily notice a common problem: Conditional Formatting in Excel (2010) are often split when inserting rows that intersect the area they're formatting.

However, this "splitting" isn't consistent: What requirement does a Conditional Formatting rule need to following in order to avoid being split?

---

Steps to Reproduce

1. New Excel document

2. In "Sheet1":

   • Write "1" in A1; "2" in A2; and so on up to A20. (I used a formula to simplify this)

3. In "Sheet2":

   • Write "2" in all the cells in the range A3:A15
   • Write "foobar" in all the cells in the range B3:B15 (except B5 and B12 which should be empty)
   • Write "1" in D1; "2" in E1; "3" in F1; and so on up to M1. (I used a formula to simply this)

4. In "Sheet3":

   • Write "3" in A1; "5" in A2

5. In "Sheet2" select D3:M15, "New Rule..." "using a formula to determine which cells to format":

   formula: =AND($B3<>"",MATCH(D$1,Sheet3!$A$1:$A$10,0))
   format: Set background FILL to bright orange.

6. In "Sheet2" select A3:M15, "New Rule..." "using a formula to determine which cells to format":

   formula: =AND(MOD(Sheet1!$A1,2)=0,$A3=2)
   format: Set a solid black line to the bottom border

7. You should see a line every two rows.

8. Select Row 8; Insert a row; Enter "2" and "foobar" in the newly inserted row. Notice how you nolonger have a line every two rows! This is due to the conditional formatting being split in two.

9. Open the "Manage Rules" (under Conditioanl Formatting). Only one of the two rules has been split. Why?!

Observations

The two Conditional Formatting rules above are similar (i.e. both refer to cells in other sheets) and yet Excel treats them differently. Why?!

---

Side note: The above example is used to explain the problem in a simplified context. Offering alternatives to "formatting every other row" does not solve the problem of understand why Excel treats some rules differently than others.