Excel static conditional formatting area

Question

I want to create a static conditional formating for the whole document (e.g. applied on A1:Z999), which is not superseeded by new intermediate conditional formattings if adding new rows in between.

In detail:

As an example I have the following table content

   |  A  |  B  |  C
 ----------------------
 1 | A.1 |     |            FIRST  LEVEL ROW
 2 |     | B.2 |            SECOND LEVEL ROW
 3 |     |     | C.3        THIRD  LEVEL ROW
 4 |     |     | C.4        THIRD  LEVEL ROW
 5 |     | B.5 |            SECOND LEVEL ROW
 6 |     |     | C.6        THIRD  LEVEL ROW
 7 | A.7 |     |            FIRST  LEVEL ROW
 8 |     | B.8 |            SECOND LEVEL ROW 
 9 |     |     | C.9        THIRD  LEVEL ROW
10 |     |     | C.10       THIRD  LEVEL ROW

I now want all rows, which have a kind of indented child row to be colored in specific way, e.g.

rows 1 and 7 in dark grey, since they have row 2 and 8 as an indented child rows (FIRST LEVEL)
rows 2,5,8 in light grey due to indented child rows 3,6,9 (SECOND LEVEL)
and so on

I can do this via the conditional formating

  FIRST LEVEL
  * apply if : AND(LENGTH($A1) > 0; LENGTH($B2) > 0)
  * apply to : $A1:$C10
  * modify   : dark grey background color

  SECOND LEVEL
  * apply if : AND(LENGTH($B1) > 0; LENGTH($C2) > 0)
  * apply to : $A1:$C10      
  * modify   : light grey background color

This works smooth without any problems.

However if I now insert two new blank rows before row 7 (creating new row 7 and 8 and shifting row 7 containing A.7 to row 9), the conditional formatting is extended by a new entry, inserting a new rule for the intermediate rows 7/8 and splitting to original one...

  FIRST LEVEL (now split)
  * apply if : AND(LENGTH($A1) > 0; LENGTH($B2) > 0)
  * apply to : $A$1:$C$5;$A$9:$C$12
  * modify   : dark grey background color

  SECOND LEVEL (now split)
  * apply if : AND(LENGTH($B1) > 0; LENGTH($C2) > 0)
  * apply to : $A$1:$C$5;$A$9:$C$12
  * modify   : light grey background color

  FIRST LEVEL (intermediate)
  * apply if : AND(LENGTH($A6) > 0; LENGTH($B9) > 0)
  * apply to : $A$6:$C$8
  * modify   : dark grey background color

  SECOND LEVEL (intermediate)
  * apply if : AND(LENGTH($B6) > 0; LENGTH($C9) > 0)
  * apply to : $A$6:$C$8
  * modify   : light grey background color

This is not correct anymore, since it doesnt check adjacend rows anymore (e.g. A6/B9 falsely used). Hence I have to modify the conditional formatting each time I add new rows, in order to restore the original behaviour applied to ALL rows.

Long story short: Is there any way to tell Excel to use a STATIC conditional formatting and simply to extend the area it is applied to instead of adding new intermediate areas?

Working on Excel 2013

That is true, I can also simply apply this to the complete row via * apply to $1:$10, but the problem remains the same. When inserting new rows, the area is again split into $1:$5;$9:$12and $6:$8 — HeXor
I can definitely reproduce the problem with Excel 2013 (15.0.4849.1003) on Windows 7 — HeXor

pnuts pnuts · Accepted Answer · 2018-01-31T20:55:42

Easily avoided by applying whole column references:

$A:$C for Range rather than $A1:$C10

and setting rules for one row at a time, ie just test which column contains a value, for example for FIRST LEVEL:

=AND($A1<>"",COUNTA($A1:$C1))

where the AND is to avoid formatting rows where all of ColumnsA:C are blank.

Excel static conditional formatting area

1 Answers