(VBA) Refresh cell if another cell in a range changes colour

[KVexcel]

So I have this function that counts cells of a color on a range

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

I want the function cell to be refreshed each time a cell on the given range changes colour. Also, is there a way to incorporate this inside the function code or does it have to be a seperate sub ?

 

[KVexcel]

Are all UDFs volatile by default when they are created ?
I looked it up here Application.Volatile method (Excel)
I added Application.Volatile to GetColorCount and it still doesn't work. Could it be that cells on the A2:B5001 range originate from the spill from the SORT in cell B2?
Or is selecting a cell irrelevant on it belonging to an array?

 

[Fluff]

Are all UDFs volatile by default when they are created ?

No they are not, they will normally only calculate when needed.

Did you put the selectionchange event into the "Easy Tags" sheets code module?

 

[KVexcel]

The modules are all like this, no module is on a specific sheet as far as I can undetstand.

Also, I'd like to thank you for all your help so far. I hope one day I learn enough about excel and VBA so I can in turn help answer other people's questions

 

[Fluff]

The change event MUST go in the relevant sheet module.

 

[KVexcel]

The folder Modules was auto-created when I created the first module. Should I rightclick and uncheck toggle folders like this?

Is there an option to put modules and sheets on the same folder ?

 

[Fluff]

No, you just need to copy the selectionchange event & paste it in the "Easy Tags" code module

 

[KVexcel]

Ohhhh now it's working! Thanks a ton for all your help

 

[Fluff]

Glad to help & thanks for the feedback.