UDF used in formula will not calculate

[jmdooley]

Using VBA I entered a function (found online) to be used in a cell formula. The function and the formula work, but the cell that contains the formula will not update automatically; Calculations are set to automatic. To make the formula work I have to place my cursor behind the formula and enter.

Excel Version: Microsoft® Excel® for Microsoft 365 MSO (16.0.13029.20342) 64-bit

My formula: =(CheckRegister[[#Totals],[WITHDRAWAL]]-SUMCOLOR(G5,CheckRegister[WITHDRAWAL]))

My UDF Code: Function SumColor(rColor As Range, rSumRange As Range)

Dim rCell As Range

Dim iCol As Integer

Dim vResult

iCol = rColor.Interior.ColorIndex

For Each rCell In rSumRange

If rCell.Interior.ColorIndex = iCol Then

vResult = WorksheetFunction.Sum(rCell) + vResult

End If

Next rCell

SumColor = vResult

End Function

[maclearyj]

Hi there. You need to tell excel that this function is volatile (i.e. calculated evry time anthing changes on the sheet. Do it like this:

Please Login or Register  to view this content.

Please be aware that having a lot of volatile functions will slow excel down.

[Andy Pope]

The formula will not be calculated if you are simply changing the colour of a cell.

The Application.Volatile should not be necessary in this case as the formula will recalculate whenever the Total row of the table recalculates.

[jmdooley]

Thank you both for your reply. The formula does recalculate based on cell color when the total column updates, my issue is I need the formula to update when the cell color changes. Often in this spreadsheet, the only change will be to the cell color, i.e. change cell color to GREEN when paid, RED when past due, YELLOW if an issue, etc. maclearyj code while very much appreciated did not resolve my issue, probably because I was not clear in that I needed the formula to update on "any" change of cell color. Any ideas on how to make this formula work when the cell or cells in the range change color?

[Andy Pope]

Changing cell colour does not raise any event.

Instead you could try using the selection change event, and from there calculate the cell.