Cannot Force UDF to Calculate

[RichardBX]

I wrote a function in VBA to count cells in a range that match the value of another specified cell. In addition, the cell is not counted if another specific cell in the same row is colored grey. The code works, but will not update unless I hide or unhide cells on one of the two sheets referenced by the formula. Clicking calculate now or calculate sheet does nothing. Selecting the cell with the formula in it and clicking enter does nothing. Since the cell color should only change when a macro changes it, I tried using code to force calculation in that macro. It doesn't work for some reason. Anyway, here is the relevant code. Let me know if there's more information needed. I want to keep the post concise, but I'm at a bit of a loss as to what could be causing it.

This is the formula I put in cell G2 on sheet3
=Checkactive(Sheet1!$C$2:$C$373, $A$2)

The following code is in Module2:
Function CheckActive(A As Object, T As Object) As String
Dim Cell As Range
Dim R As Integer
Dim total As Integer
Dim WS As Worksheet
Dim Tool As String
total = 0
Tool = T.Value
For Each Cell In A
If Cell.Value = Tool Then
R = Cell.Row
Set WS = Cell.Worksheet
If WS.Cells(R, 1).Interior.Color <> RGB(128, 128, 128) Then total = total + 1
End If
Next Cell
If total > 0 Then
CheckActive = "Yes"
Else
CheckActive = "No"
End If
End Function

The following code is in Sheet1. The bolded lines are relevant.
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Column = 1 And Target.Row <> 1 Then
Cancel = True
Dim R As Integer
R = Target.Cells.Row
If Target.Cells.Interior.ColorIndex <> 16 Then
Target.Cells.Interior.ColorIndex = 16
Cells(R, 2).Interior.ColorIndex = 16
Cells(R, 2).ClearContents
Cells(R, 12).Interior.ColorIndex = 16
Cells(R, 20).Interior.ColorIndex = 16
Exit Sub
End If
If Target.Cells.Interior.ColorIndex = 16 Then
Target.Cells.Interior.Color = xlNone
Cells(R, 2).Interior.Color = xlNone
Call Refresh_Date
Cells(R, 12).Interior.Color = RGB(169, 208, 142)
Cells(R, 20).Interior.Color = RGB(255, 67, 67)
Exit Sub
End If
Workbook.RefreshAll
Workbook.Calculate
End If
If Target.Column = 12 And Target.Row <> 1 And Target.Cells.Interior.ColorIndex <> 16 Then
Cancel = True
Call Send_Data
End If
If Target.Column = 13 And Target.Row <> 1 Then
Cancel = True
Call Refresh_Average
End If
If Target.Column = 20 And Target.Row <> 1 And Target.Cells.Interior.ColorIndex <> 16 Then
Cancel = True
undowarning.Show
End If
End Sub

 

[RoryA]

Add:

Application.Volatile True

to the start of the function. You will still have to press f9 or force a recalculation after colours have been changed.

 

[RichardBX]

Add:

Application.Volatile True

to the start of the function. You will still have to press f9 or force a recalculation after colours have been changed.

Doing this makes the function work correctly once, but if I change the cell color again I get a #VALUE! error. As you can probably tell from my code, I need to be able to toggle the cell color between grey and nothing and have the function update related cells correctly. Saying it like that makes me realize I should probably write my toggling code to update the cells I need it to without the use of a function. It's a difficult workaround, but is probably easier than this mess. Still, if you or anyone else can tell me what is going on with this code I would be very happy to learn.