UDF Not Recalculating?

[All_At_Sea]

Good Morning,

I have the following code in a Module: -

Function CountColor(rng As Range, colorcell As Range) As Long
    Dim cell As Range
    Dim clr As Long
    clr = colorcell.Interior.Color
    For Each cell In rng
        If Evaluate("GetColor(" & cell.Address(External:=True) & ")") = clr Then
            CountColor = CountColor + 1
        End If
    Next cell
End Function

Function GetColor(cell As Range) As Long
    GetColor = cell.DisplayFormat.Interior.Color
End Function

With the following sheet: -

Life Ex Date Count Test.xlsm
	A	B	C	D	E	F
1
2
3					2
4					1
5
6
Sheet1

Cell Formulas
Range	Formula
E3	E3	=CountColor(B3:B5,D3)
E4	E4	=CountColor(B3:B5,D4)

The UDF works fine each time I manually edit the Formula and press enter. However, if I then change the Color in one of the Cells in Column B, the result/count in D doesn't update/recalculate.

I've checked and Calculation Options is set to Automatic and tried forcing it to Recalculate by pressing both the Calculate Now and Calculate Sheet buttons on the Ribbon.

I'm stumped as to the cause.

Any ideas?

 

[Rick Rothstein]

Changing a color in a cell manually does not trigger any events for VBA so Excel does not see any "dirty" data that it has to refresh for. To the best of my knowledge, there is nothing you can do to overcome this. One thing that you can do to make your function almost work automatically is to create a SelectionChange event on the sheet that recalculates the sheet... change the color will still do nothing, but once the use moves to another cell, the function will update correctly.

 

[Kaper]

The change of a cell interior color does not tell Excel to recalculate. Neither Excel notices something to be calculated appeared if only the color has changed, so pressing F9 for manual calculation does not work too.

If forcing calculation (by pressing F9 of from the ribbon or ... by deleting an empty cell, etc.) is enough then add Application.Volatile to your function, like:

Function CountColor(rng As Range, colorcell As Range) As Long
    Dim cell As Range
    Dim clr As Long
    Application.Volatile
    clr = colorcell.Interior.Color
' ...

The other idea (edit already mentioned by @Rick Rothstein is to use Change event fo sheet, so right click on the sheet tab and in your sheet code (not in general module) insert:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ActiveSheet.Calculate
End Sub

But this will act that way, tah when you just change color the function is not recalculated yet. recalculatin happens, when you select another cell (with a mouse or cursor keys or with Enter)

 

[All_At_Sea]

Many thanks @Rick Rothstein and @Kaper for the replies and information.

I've tried both methods as detailed by @Kaper but unfortunately neither of these have worked?

Here's what I've added for the Change Event: -

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    ActiveSheet.Calculate
    
End Sub

Even the Application.Volatile as below doesn't appear to work when using F9 or the Ribbon: -

Function CountColor(rng As Range, colorcell As Range) As Long
    Dim cell As Range
    Dim clr As Long
    Application.Volatile
    clr = colorcell.Interior.Color
    For Each cell In rng
        If Evaluate("GetColor(" & cell.Address(External:=True) & ")") = clr Then
            CountColor = CountColor + 1
        End If
    Next cell
End Function

Is there something I'm missing in my Excel Options or with the Code?

 

[All_At_Sea]

After so Googling and trial and error I have found a solution which thankfully works.

I added this into ThisWorkbook: -

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Target.Calculate
End Sub

Works every time!

Thanks again @Rick Rothstein and @Kaper for your earlier responses and time to have a look.

 

[Kaper]

Basically it is the same solution, but the code is in ThisWorkbook module rather than in Sheet module. The side effect (could be bad if there are sheets with a lot of formulas) is that it runs in all sheets.

As a matter of fact, I've tested it befor publishing post #3, and it worked.

 

[All_At_Sea]

Thanks @Kaper , I've changed it as suggested just in case I add further formulas.