Selected Answer
I was able to reproduce the error using your workbook. Research shows, however, that there shouldn't be a problem. There are 64,000 different formats available in an Excel 20007 (and later) workbook. I suggest you try again with a totally new one.
To better understand the problem please refer to this link. They offer a tool for cleaning up an older workbook as well.
Anyway, if you can't get the solution to work I suggest you try to make friends with the other one. It's not as delightful as this one but it should be able to do the job.
Edit 04 Aug 2018 =================================
Sorry to reply to your discussion here. I can't the Add to Discussion button to respond. I have been imagining a work-around but its a major job - a few days of research and coding. I think the benefit isn't worth the investment. The other solution I already posted just requires you to understand that update takes place when you leave the cell, not when you modify it.
The other reason why I don't want to invest the time is that your workbook was designed to ask a question. A working solution must be applied to a workbook which was designed to do the job. The worksheet/workbook design must be settled and final before work might commence. This means that you should fine-tune your worksheet and work flow with the VBA solution you now have and only then look for improved code. My guess is that by the time you have done the former you may not feel the need for the latter lol: