Find "invisible" conditional formatting

[IanC]

I have created a number of templates in Excel 2000 which have undergone
numerous changes over their lives. When opened in Excel 2007, a number of
cells are changing colour because of confitional formatting. This formatting
does not exist as far as Excel 2000 is concerned.

Is there some way in Excel 2000 that I can find cells with this hidden
formatting? Alternatively, is there a VBA method of finding all cells
apparently without conditional formatting, then deleting all conditional
formatting from these cells? It'll take an age to go through each template
manually.

 

[Bob Phillips]

Why would it not show in Excel 2000?

Could you select all cells and just delete any and all conditions?

 

[IanC]

Hi Bob

I have no idea why it doesn't show in 2000. It's very peculiar. Over the
years the templates have undergone continuous modifications, including many
changes to conditional formatting (conditions deleted, others added etc).

I can't simply select all cells and delete the conditional formatting as
there are many cells with conditional formatting that I wish to keep.
Unfortunately, I don't have access to Excel 2007 so I can't open the
templates in that and check visually which cells are affected.

What I need is some way to determine what cells Excel 2000 *thinks" are
empty of conditional formatting, then to delete all conditions from these
cells. Oddly, that seems to cure it, even though Excel 2000 showed the cells
as having no conditions set.

Any thoughts?

 

[Bob Phillips]

Can you post the workbook somewhere and point me at some of these 'hidden'
formats? I have 2000 and 2007, I would like to take a look at it.

 

[IanC]

Hi Bob

Sorry for the delay. Been on holiday

I've never uploaded a file for remote access. Can you suggest somewhere?

 

[IanC]

Hi Bob

Ignore my earlier message. File_For_Bob_P.xls is on www.qdrive.net.
Depending on subsequent traffic you may find the file on the list of
recently shared public files. If not, click on "View All Shared Files" and
use the search facility.

I've highlighted the cells in yellow which show red text in Excel 2007, but
black (as they should be) on Excel 2000.

There is a bunch of code but you shouldn't need to enable the macros and
there are links to an external workbook, but you won't have that or need it.

I hope all this makes sense when you see the file.