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

 

[NoviceCoder]

Are the cell colours being changed manually?

yes the cell colours are being changed manually. Looking at the thread so far it looks as though selection change maybe the best solution. Is there a way in which selection change in rows 1-7 (week 1) changes updates the formula in those rows only. And the same in rows 8-14 (week 2), etc. I don't want to recalculate all formula in the entire worksheet if the summary data only relates to a particular week.

 

[Fluff]

It will recalculate every formula on that sheet.

 

[NoviceCoder]

It will recalculate every formula on that sheet.

I am very grateful for your help. I am sorry but i don't see the code and the XLB22 hyperlink just sends me to a generic add-in page

 

[Fluff]

I am sorry but i don't see the code

That's because nobody has posted any code.
What is the range of the cells being coloured?

 

[NoviceCoder]

That's because nobody has posted any code.
What is the range of the cells being coloured?

Sheet 4 D14:DQ18

 

[Fluff]

How how about

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   If Not Intersect(Target, Range("D14:DQ18")) Is Nothing Then
      Me.Calculate
   End If
End Sub

 

[NoviceCoder]

Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Not Intersect(Target, Range("D14:DQ18")) Is Nothing Then Me.Calculate End If End Sub

great thank you

 

[Fluff]

Glad we could help & thanks for the feedback.