I have a worksheet that is comparing a bunch of text against a template and coloring the box based on how similar it is to the template. I needed to get a code that could count how many are green(good) and how many are red(bad) and I then have this number referenced in the back which is further used to create a pivot chart in the first sheet. My issue is that when I first open the file I get these #VALUE! errors seen below (https://i.sstatic.net/GPd2sI6Q.png) and it really confuses me because I click the box and hit the enter button and the code runs just fine and gives me the results below (https://i.sstatic.net/Yj8CJiCx.png) I am using the code below to count the colors

Function GetColorCount(CountRange As Range, CountColor As Range)
Dim CountColorValue As Integer
Dim TotalCount As Integer
Dim rCell As Range
CountColorValue = CountColor.Interior.ColorIndex
Set rCell = CountRange
For Each rCell In CountRange
  If rCell.Interior.ColorIndex = CountColorValue Then
    TotalCount = TotalCount + 1
  End If
Next rCell
GetColorCount = TotalCount
End Function

and interestingly enough when I hit enter on the row that gives me the “compliance green” number it also runs “compliance red” and has no issues giving the correct number

I have ensured that the calculation option is set to automatic. I have no idea what is causing this to happen? Any ideas? I am using Excel version 2405 btw