I have a function i got off of MSDN that counts the number of cells in a range that have a another cells color.
Here is the code
Function countCcolor(range_data As Range, criteria As Range) As Long
Application.Volatile
Application.ScreenUpdating = False
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
Application.ScreenUpdating = True
End Function
A requirement for this function is that it will update when ever a color value of a cell changes.
My idea is to create an event for when ever the color of a cell is changed and have it recalculate the any cell with the function but I'm not sure if this is the best way.
解决方案
You may have already found out that changing a cell's interior color doesn't fire a Sub Worksheet_Change(...). As no value is changed, nothing gets recalculated. In this case not even the Application.Volatile is of help.
The best approach might be to use a Worksheet_SelectionChange(...), eventually in combination with Worksheet_Activate(...) and Worksheet_Deactivate(...) (to have the sheet clean upon entry and leave) to call for an enforced recalculation, e.g.
Sub DoMyRecalc()
' Range("OutputRange").Calculate ' all uses of countCcolor() within that range
' [H3].Calculate ' countCcolor() only used in cell H3
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
DoMyRecalc
End Sub
Private Sub Worksheet_Activate()
DoMyRecalc
End Sub
Private Sub Worksheet_Deactivate()
DoMyRecalc
End Sub