Processing
 

Excel static conditional formatting area

31/08/2016 09:06#1

HeXor

Member

Joined at: 6 months ago

Post: 1

Thank: 0

Thanked: 0

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

Similar articles

Excel More conditions in one formula?

6 months ago - Reply: 2 - Views: 172

Get Workbook reference from Open File dialog

6 months ago - Reply: 2 - Views: 131

Add Calculated Field To Pivot Table

6 months ago - Reply: 0 - Views: 7

Nested IF Functions in Excel not working

6 months ago - Reply: 3 - Views: 164

Formulating Date in Excel 2013

7 months ago - Reply: 2 - Views: 88

VB6 extracts in Excel 2013

7 months ago - Reply: 2 - Views: 153

Excel 2013 : Pull Matching Data from Column

7 months ago - Reply: 1 - Views: 23

excel search and copy in/from another file

7 months ago - Reply: 0 - Views: 1

Buggy PivotTable.ColumnRange

7 months ago - Reply: 0 - Views: 6

Excel static conditional formatting area

7 months ago - Reply: 0 - Views: 8

vé máy bay vé máy bay giá rẻ ve may bay ve may bay gia re vé máy bay vé máy bay giá rẻ ve may bay ve may bay gia re vé máy bay vé máy bay giá rẻ ve may bay ve may bay gia re vé máy bay vé máy bay giá rẻ ve may bay ve may bay gia re vé máy bay vé máy bay giá rẻ ve may bay ve may bay gia re vé máy bay vé máy bay giá rẻ ve may bay ve may bay gia re vé máy bay vé máy bay giá rẻ ve may bay ve may bay gia re