Problem discovered when using a macro to copy and paste cells with conditional formatting.

Issue: When using a copy and paste macro for cells with conditional formatting, the paste function does not overwrite the conditional formatting to the target cells, instead it adds additional "rules" to the target cell, effectively changing the conditional formatting rules . This occurs even when the two cells have the same conditional formatting prior to the copy and paste function.

Example: Cell A1 on sheet 1 has conditional formatting-Turns RED if cell value is over 30, Turns Green is cell value is under 30

               Cell A1 on sheet 2 has conditional formatting-Turns RED if cell value is over 30, Turns Green is cell value is under 30

When a macro is used to copy and paste cell A1:sheet 1 into cell A1:sheet 2, the program adds new rules and changes the existing rules for the conditional formatting. The result of this creates extra formatting rules everytime the copy and paste function macro is used.

I have resolved this by using a paste special "paste values" instead of paste function in the macro. The copy and paste function (paste all) should overwrite the conditional formatting in the target cell...no?

Has this issue been brought up before? This seems to be a root error within Excel itself. The problem seems to be compounded when the workbook is transferred via email from one computer to another. I have experienced this with both Excel 2007 and 2010.

Bo Kellar

Hi,

This is the forum to discuss questions and feedback for Microsoft Excel, I'll move your question to the MSDN forum for Excel

http://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev&filter=alltypes&sort=lastpostdesc

The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

George Zhao
TechNet Community Support