Excel 2010 deleting my conditional formatting on either close or open...

[jmwbowen]

I have a problem where Excel 2010 is deleting one of my conditional formatting conditions. I do not know if it is deleting it on open or close. I just know that I enter the conditional formatting, save the workbook, close the workbook, reopen the workbook, and it is gone.

The other conditional formatting conditions (simple "Cell is Blank") are left intact.

Setup:

Cell with conditional formatting is on sheet "MyRecord" at "$E$7". A corresponding boolean value on separate sheet "Data" and must be referred to by using "OFFSET" with a record locator value (workbook level named range "RecordLocator") that leads to corresponding data row, so looks like following:

=IF(OFFSET(Data!$D$5,RecordLocator,0),TRUE,FALSE)

When I configure this, it works great until I save and close the workbook. When I reopen the workbook, this particular conditional formatting is gone with all other conditional formatting left intact.

I've tried using the actual range address in place of range name "RecordLocator", changes nothing.

I can't figure out why Excel deletes this particular conditional format. Help?

 

[njimack]

Are you actually saving it as a 2010 file (xlsx or xlsm extension) as opposed to a 2003 file (xls extension)

 

[jmwbowen]

Neil,

Thanks for pointing that out. The file is being saved as a 2003 file (.xls extension).

Update: I figured out a workaround where I place:

=OFFSET(Data!$D$5,RecordLocator,0)

in a hidden cell on the same page as the cell with the CF condition, then just reference that hidden cell to determine TRUE or FALSE.

Perhaps the limitation of 2003 file is that CF cannot refer to other sheets. I will try changing the file format to 2007/2010 version.

 

[jmwbowen]

Neil,

That did it!!!

I was saving the file as the wrong type. Updating the file to 2007/2010 workbook allowed the original CF to stick.

Thanks.