Conditional Formatting Issue when using VBA

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

August 28th, 2013 12:45pm

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

Free Windows Admin Tool Kit Click here and download it now
August 28th, 2013 11:12pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics