datax的udf_替代Application.Volatile自动更新UDF

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