Update calculation of user defined formulae

[NoviceCoder]

Hi
I've encountered a frustrating and seemingly simple problem. I have a user defined formula that counts colloured cells in excel. But the calculation does not update when cells are changed like they would would standard excel function like "Sum" or "Countif". The "application. volatile" doesn't fix this and the only way i can update is to selection the cell and re-enter the fourmula. Ive seen some comments online but none seem to resolve this fairly basic problem. Any help very much appreciated. Thank you

Function COUNTIFCOLOUR(Colour As Range, rng As Range) As Long

Application.Volatile

Dim NoCells As Long
Dim CellColour As Long
Dim rngCell As Range
CellColour = Colour.Interior.Color
For Each rngCell In rng
If rngCell.Interior.Color = CellColour Then
NoCells = NoCells + 1
End If
Next
COUNTIFCOLOUR = NoCells
End Function

 

[Joe4]

But the calculation does not update when cells are changed

What kind of changes are we talking about, exactly?
How are the cells being updated?

 

[Fluff]

The only way would be to have a worksheet selectionchange event that forces the sheet to recalculate.

 

[NoviceCoder]

What kind of changes are we talking about, exactly?
How are the cells being updated?

Its an activity tracker and each cell represents a block of time. depending on the activity the cell colour is modified. The cells contains UDF (countifcolour) summarise the number of cells for each activity.

 

[NoviceCoder]

The only way would be to have a worksheet selectionchange event that forces the sheet to recalculate.

The sheet is an activity tracker with a row for each day of the week and a column for each 10min block. each cell is colour coded by the activity. The grid is replicated week after week. Besides each week is a series of cells summarising activity according to activity .e.g. activity A (colour red) may be 10 cells or 5%. So I would only want selection change in part of the worksheet to trigger the related summary cells for each activity. I hope that's makes sense.

 

[Fluff]

Are the cell colours being changed manually?

 

[mikerickson]

The problem is that changing a cells color doesn't trigger calculation or a worksheet Change event (or any event).
Even Fluff's SelectionChange workaround wouldn't be current until the user selected a different cell.

 

[RoryA]

It would be much better to put a value into the cells representing the activity type, then have the colours change based on the value. Then your formulas are simple COUNTIF formulas.

 

[NoviceCoder]

It would be much better to put a value into the cells representing the activity type, then have the colours change based on the value. Then your formulas are simple COUNTIF formulas.

Yes i agree but the coloured box concept is very visual and its a routine that multiple users are familiar with and are reliable at performing. I did consider getting cells to be filled with a number with font colour of the background and then the Countif function could be used. To achieve this i created code with a shape that appeared when a time cell was selected and the shape included a panel of coloured box/shapes (This was a kind of user defined pop-up coloured Form). When appropriate coloured box/shape was clicked the time cell colour would change (and the number would be [secretly] inserted too. It was quite elaborate and worked but was prone to bugging.

 

[RoryA]

Then you're pretty much stuck with the limitations of your current function.