Calculations are not updating

[GMC The Macro Man]

Hi Team,
I'm working on Excel 2021 and I'm having an issue with a calculation that does not update automatically or even when I click on Refresh Data. The "Calculation options" is set to Automatic and the only way I can update it, is to click in the cell, move my cursor to the end of the formula and hit the return button.
This is what I am trying to do in the table below as it shows a list of user ID's that have been sent an email invitation to 9 events over different times and regions. When their email responses are received, they are transferred over to the spreadsheet and their User ID is assigned a corresponding colour, accepted =GREEN, Declined=RED..etc. (This is done manually as it's less than 200 users)
Could someone please help me to understand why this isn't updating the count.. Thanks as always

I am using the following formula to count the colours - (Is there an easier way)?
In Cell D3 I have =GetColorCount(A5:A150,D2) which I have as a VBA script

Function GetColorCount(CountRange As Range, CountColor As Range)
Dim CountColorValue As Integer
Dim TotalCount As Integer
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

 

[RoryA]

This is why I always tell people not to use colour as data. Changing the colour of a cell does not trigger recalculation. The best you can do with this is to make it volatile by adding:

Application.Volatile True

to the start of the function code. It will then recalculate whenever any open workbook does (eg if you press f9 or another formula updates). It still won't update just from changing a cell colour though.

 

[Georgiboy]

Could you not have a value in a dropdown instead, you could add conditional formatting to format the text to go the same colour as the background of the cell, so effectively chosing a value in the dropdown will change the colour of the cell.

You could then replace the whole function for counting colour with a simple COUNTIF formula.

File attached

Count Colour.xlsx

 

[GMC The Macro Man]

This is why I always tell people not to use colour as data. Changing the colour of a cell does not trigger recalculation. The best you can do with this is to make it volatile by adding:

Application.Volatile True

to the start of the function code. It will then recalculate whenever any open workbook does (eg if you press f9 or another formula updates). It still won't update just from changing a cell colour though.

Cheers Rory - I know what you mean by using Colour but it's what the client wanted and I thought it would be an easy fix. Adding your code and refreshing with F9 does the trick.
Thanks again

 

[GMC The Macro Man]

Could you not have a value in a dropdown instead, you could add conditional formatting to format the text to go the same colour as the background of the cell, so effectively chosing a value in the dropdown will change the colour of the cell.

You could then replace the whole function for counting colour with a simple COUNTIF formula.

File attached

Count Colour.xlsx

Cheers Georgiboy, I will look in to this to see if it's a viable option.
Thanks again