Have a UDF active when the workbook is open.

[msquared99]

OK, I have this code that counts cell colors. It is in a module. What I want to do is on the worksheet where the function will be used is to have it active so when the user changes a color in a range of specified cells the function automatically runs. Much like if you have COUNT or SUM in a range and change a number, it auto calculates.

At this time, the UDF only works when you re-enter it in a cell. Basically I want the UDF active all the time.

Function CountCcolor(range_data As Range, criteria As Range) As Long
 
  Dim datax As Range
  Dim xcolor As Long
xcolor = criteria.Interior.ColorIndex
For Each datax In range_data
  If datax.Interior.ColorIndex = xcolor Then
  CountCcolor = CountCcolor + 1
  End If
Next datax
End Function

 

[Artik]

There is no simple solution. Excel no event responsive to the color change of the cell. But you see this solution:
https://sites.google.com/site/e90e50fx/home/sum-by-color-calculate-when-color-changed

Artik