NoviceCoder
New Member
- Joined
- Apr 26, 2016
- Messages
- 20
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
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