Help?? After I run a macro, cells with "macro-formulas/functions" show "#VALUE!" until I type something in a cell and hit enter

[de_keda]

Hi! I am a novice to excel macros, and my first time posting here, but have learned much over the last few months, in large part due to this site! Thanks for everyone's help.

On the workbook I'm working on now, I have several macros that need to be run throughout the day to import new data into the workbook. Lots of the data comes with conditional color formatting, and I imported the "modColorFunctions" module from Chip Pearson to identify and count cells with conditional color formatting. Then I used a formula in specific cells to call the function and identify/count those cells.

Everything works great, until I run another macro to import more data. The cells with the formula to call the function and count the conditionally colored cells show a value of "#VALUE!". BUT when I type anything into ANY cell in the workbook and hit enter, the function cells then calculate everything correctly.

I'm building this workbook for a large group and don't want them to have to have a "work-around" every time they import new data. Any idea how to make excel run these functions automatically?

The "function-cells" have the following formula, where "Countcolor" is the function:
=IF(C20<>"",(Countcolor($J25,3,FALSE)+Countcolor($AF25,3,FALSE)+Countcolor($AF25,6,FALSE)+Countcolor($AI25,3,FALSE)+Countcolor($AK25,3,FALSE)+Countcolor($AL25,3,FALSE)+Countcolor($AE25,3,FALSE)+Countcolor($AE25,6,FALSE)+Countcolor($AR25,3,FALSE)),"")

 

[de_keda]

Found the answer:
I just tacked on "Application.Calculate" at the end of the VBA macro that was causing the problem, and it worked.

I also found the following:
<code>'recalculate all open workbooks
Application.Calculate

'recalculate a specific worksheet
Worksheet(1).Calculate

' recalculate a specific range
Worksheet(1).Columns(1).Calculate

'recalculate everything
<code>Application.CalculateFull</code></code>