Excel 2010 - Macro to transfer conditional formatting rules from a corrupt file

Hello all,

I have a corrupt Excel 2010 macro-enabled workbook (Win 7) that was originally created using Excel 2006 many years ago.  The file is 6 MB, contains 35 worksheets, 500 named ranges, 400k cells with formulas, 50k formatted cells, 2k cells with unique conditional formatting (CF) rules, as well as macros and user forms.

The corruption can be seen in many erroneously formatted cells in several of the worksheets.  I've used Excel's "Open and Repair" feature and received the generic response: "Excel completed file level validation and repair.  Some parts of this workbook may have been repaired or discarded."  The log indicates:

<recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
file:///C:/Users/Me/AppData/Local/Temp/error084120_01.xml#

Some of the "repaired" cells have lost all of their formatting, while some CF rules have been deleted.  However, it's impossible to determine the extent and location of every repair.  When I save and close the repaired file, some cells' borders randomly change upon re-opening!

The safest approach (I think) is to start with a blank workbook and duplicate the contents of the corrupt file's cells.  I can create macros to copy cell formulas and named ranges, but copying the conditional formatting rules is problematic.  If I use "Copy & Paste" as the transfer mechanism within the macro, I'm concerned that the corruption may be transferred to the new workbook.  So my questions are:

1.  What is the most efficient and effective approach to duplicate the CF rules in the new workbook (without transferring corrupt contents)?
2.  What causes file-level corruption and individual cell CF rule corruption?  (i.e. why are random borders created around cell X15, but cell X16 remains undisturbed).
3.  How can the corruption be avoided in the future?

Any guidance would be greatly appreciated.

June 30th, 2015 2:34pm

Maybe you need this atricle:

http://blog.contextures.com/archives/2012/06/21/excel-2010-conditional-formatting-nightmare/

Free Windows Admin Tool Kit Click here and download it now
July 7th, 2015 5:10am

Thanks for the tip.  That article suggests the use of macros to create new conditional formatting rules when the formatting will follow a pattern or when inserting new rows, but it doesn't address how to duplicate rules from a corrupt workbook to a new workbook.  I'll post back if I come across a solution.
July 9th, 2015 11:35am

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

Other recent topics Other recent topics