Excel 2007 conditional formatting bug?

[John]

I seem to have run across a bug in Excel 2007 regarding conditional
formatting. If you insert a row in a table that has conditional formatting
rules applied, and then copy another row that also has conditional formatting
rules, Excel will add the new conditional formatting rules, rather than
replacing them. So, if you started with 3 conditional formatting rules, then
after the first iteration, you now have 6, then 9, then 12, etc. This quickly
gets out of hand and performance degrades quickly.

Here is the procedure for recreating the bug:

- Open a new Excel workbook in Office 2007
- Select cells A1:H1
- Apply formatting to the selected cells to put a border around the cells
(only did this so that I could see the cells I was working with)
- Apply conditional formatting rules to the cells
- If cell value is < 0, turn text blue
- If cell value is between 0 and 1, turn text green
- If cell value is greater than 1, turn text red
- Hit CTL-C to copy the cells
- Select cells A5:A6 and hit CTL-V to paste the cells to rows 5 and 6
- Select row 6 (entire row selected)
- Insert a new row
- Select row 1 (entire row selected) and hit CTL-C to copy the row
- Select A6 (this is the row you just inserted) and hit CTL-V to paste the
contents of row 1 into this row
- Select A6 (i.e. deselect the entire row)
- Review the conditional formatting rules for this cell. Note that the
number of conditional formatting rules has been doubled. Additional
conditional formatting rules will be added each time this procedure is
repeated.

I have an Excel application that uses a macro to automatically do this
because I want a specific row template applied when the user inserts a new
row. This template changes, based upon the type of data the user is intending
to insert.

Is there a way around this problem? Is there an option I can add to the
paste command that will replace the conditional formatting rules, rather than
adding additional rules?

Thanks,
John

 

[Bob Phillips]

As I replied at OzGrid, I think this is by design, not a bug.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Lars-Åke Aspelin]

I seem to have run across a bug in Excel 2007 regarding conditional
formatting. If you insert a row in a table that has conditional formatting
rules applied, and then copy another row that also has conditional formatting
rules, Excel will add the new conditional formatting rules, rather than
replacing them. So, if you started with 3 conditional formatting rules, then
after the first iteration, you now have 6, then 9, then 12, etc. This quickly
gets out of hand and performance degrades quickly.

Here is the procedure for recreating the bug:

- Open a new Excel workbook in Office 2007
- Select cells A1:H1
- Apply formatting to the selected cells to put a border around the cells
(only did this so that I could see the cells I was working with)
- Apply conditional formatting rules to the cells
- If cell value is < 0, turn text blue
- If cell value is between 0 and 1, turn text green
- If cell value is greater than 1, turn text red
- Hit CTL-C to copy the cells
- Select cells A5:A6 and hit CTL-V to paste the cells to rows 5 and 6
- Select row 6 (entire row selected)
- Insert a new row
- Select row 1 (entire row selected) and hit CTL-C to copy the row
- Select A6 (this is the row you just inserted) and hit CTL-V to paste the
contents of row 1 into this row
- Select A6 (i.e. deselect the entire row)
- Review the conditional formatting rules for this cell. Note that the
number of conditional formatting rules has been doubled. Additional
conditional formatting rules will be added each time this procedure is
repeated.

I have an Excel application that uses a macro to automatically do this
because I want a specific row template applied when the user inserts a new
row. This template changes, based upon the type of data the user is intending
to insert.

Is there a way around this problem? Is there an option I can add to the
paste command that will replace the conditional formatting rules, rather than
adding additional rules?

Thanks,
John

A way around would be to, before pasting the row whose formating you
would like to be kept into an area with a formating that you do NOT
want to keep, delete the formating with the following statement

Selection.FormatConditions.Delete

Hope this helps. / Lars-Åke