Excel 2007 Crashing Using Named Range in Conditional Formatting

[Littlenuts]

This has been a real frustrating one and I have not been able to find anyone that has found a solution to it.
I have one simple (but large table). It has dates across the top (formatted in hh format). I would like the associated table to format according to the day and also to format differently when there is a public holiday. So I have built a table with the holidays and named the relevant cells as "Holiday_Valid".
I have the following formulae in the conditional formatting;

Please Login or Register  to view this content.

Where D11 has the current day in question and Holiday_Valid is a list of public holidays. Since there is a cell for each hour of each day I am using "int()".
There is a second conditional format to format Sundays differently as below;

Please Login or Register  to view this content.

My problem is that these both work well....but then after a few minutes the whole sheet crashes with those dreaded "Trying to recover your data" and "Excel will restart" etc.
I have removed references to named ranges and so far - so good....but this means putting the validation table in the same sheet as the main table. In the past I have been able to use named ranges (albeit not in such convoluted formulae), but now it seems that it is not working any longer.
When I open the recovered sheet, all the conditional formatting has been removed and the message from the repairs is that there was some invalid conditional formatting.
Extensive web searches did show some issues with conditional formatting using names ranges....especially with frozen panes....which I need use with a sheet this big.
Any hints or assistance would be much appreciated....

Pierre

[Pauleyb]

Sounds like you are running into some sort of bug. Maybe these will help:
1) Start over. Yes, you can copy paste data from sheets, but do not copy/move sheets to the new workbook.
2) Hard code the Holiday_Valid dates within the Name Manager. This won't make it as easy to update, but you would not be referencing a sheet (assuming that is one of the problems).

[Richard Buttrey]

Hi,

Just an idle thought and wondering if there is some conflict in evaluating the CFs. Do they have any sort or priority? Have you tried swapping them round and/or checking the Stop If True option?

[Littlenuts]

Thanks PauleyB I have tried rebuilding from scratch but the same has occured....again not immediately. The formatting works and the whole sheet works for a while and then appears to crash at a random time. If I am able to recover any thing and look at the "repairs" it mentions CF conflicts and all CF has been removed from the recovered file.....

[Littlenuts]

Richard....I am sure you are correct....there is some conflict. I tried removing the reference to a named range in a different tab and put the date lookup into the same sheet as the conditional formatting and to use a static range definition. I thought that this had "cracked" it as I was able to work for a few days.....but then the crash again. Same result. The recovered file had all the CF stripped and the repair mentioned CF conflict. What I will try now is to add some rows and perform the CF formulae in these cells "=if(text(<date_reference>,"ddd")="Sun",true, false)" etc. and then just use the CF on the cell result.

p.s. sorry for late response, I had to take a business trip and was not able to work on this project for a week.

Thanks for your pointers.