Excel 2013 "Calculation is incomplete. Recalculate before saving?"

For me the problem happens only in a certain sheet and it seems also related to conditional formatting which is depending on a custom Function (defined in VBA). By setting breakpoints I found out that the function is executed repeatedly every few seconds (which makes not much sense). This also causes a wrong displaying of part of the section: The numbers and text in the fields and the borders of the fields just vanish, not everywhere where the conditional formatting is, but randomly in the near area (even field which are not used to calculate the conditional formatting nor have any conditional formatting themselves). Scrolling up and down makes text reappear which is not used for calculation nor has conditional formatting applied to and the other text/numbers and borders appear for about a second and then vanish again.

To work around this weird problem I simplified the conditional formatting, so it would no longer directly depend on a custom formula (EDIT: I overlooked another conditional formatting which depended on a custom formula, so the bug still was active. Also it seems that there need to be at least 2 conditional formattings in the region to cause this bug? I solved the problem for me now by completely by having no conditional formatting depend directly on a custom VBA-formula, but rather cells which contain this formula.), but on cells which use the custom formula. Since there is was not enough space, you need to scroll to the side to see these Formulas. Now I can do the following: I scroll to the cells which do the calculation for the conditional formatting with my custom formula. I click in any cell on the screen (it can be also an empty cell). Then I click in the line where I can write in formulas (next to the "fx") and press the Enter key. I did not change anything, but this causes the continuous re-calculation to finally finish. Then I scroll back and the cells appear as they should be and I can save without the repeated Error message that I need to recalculate...

That also works the other way around: When the screen now shows the cells affected by conditional formatting and  I click in any cell on the screen (it can be also an empty cell). Then I click in the line where I can write in formulas (next to the "fx") and press the Enter key. Now the Bug turns on again and the view is messed up again and saving causes the repeated "Calculation is incomplete. Recalculate before saving?"-Error again.

Being able to reproducibly turning off and on the bug without making any changes to the workbook suggests that there is a serious bug in the way 2013 calculates conditional formatting. I read an article claiming that it would execute the conditional formatting 6 times in an experiment, in this case it is repeating it without stopping.

Please Fix!

Although helpful in the sense that it properly analyses the problem, I cannot work around it so easily, so I agree with the last line of the post: Please Fix!

I use a simple trick that used to work like a charm to make fields that can be filled in stand out. I created a VBA function that returns true if the cell passed as argument is locked and then create a conditional formatting rule using this function to change the background color for each locked cell. Obviously I cannot easily do this with an intermediate cell.

So again: Please Fix!

Jan

Although helpful in the sense that it properly analyses the problem, I cannot work around it so easily, so I agree with the last line of the post: Please Fix!

I use a simple trick that used to work like a charm to make fields that can be filled in stand out. I created a VBA function that returns true if the cell passed as argument is locked and then create a conditional formatting rule using this function to change the background color for each locked cell. Obviously I cannot easily do this with an intermediate cell.

So again: Please Fix!

Jan

Lets assume A2 is the Top-Left most cell you want the conditional Formatting to be applied to, the just write in the formula "=A1<>""" or "=A1=""" 

Then apply this to all the ranges you need e.g. $A$1,$A$3,$B$2,$B$5:$C$9 (choose them with pressed CTRL)

And the formatting will work without custom VBA-Function.

BTW: I found that if the workbook is open and the bug is active, that the Error when saving also comes when I try to save a different open workbook! The other workbook is having (almost) no formatting and just a few simple charts and no VBA. Then I turn the bug off again in the conditional formatting workbook (as described previously) and then I can save the normal workbook. WTF?

I'd be interested in seeing the VBA functions code, perhaps it contains logic that can be improved so 2013 will also work with it?