I am getting #VALUE! and I dont know how to fix it

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 Color error before.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 color error after.PNG I am using the code below to count the colors

Please Login or Register  to view this content.

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

I dont know how to close the threads but I did figure out the issue, thank you for taking the time to respond. My issue was that the range I was looking at for the colors included the entire column and I placed the "result" of my formula inside of that same column and it made a circular reference. If anyone else sees this in the future, that may be your problem.