Help! Colour count...why does not work?
Posted by arpan on August 24, 2001 2:45 PM
Hello can anyone help me?
How can i re-calculate the sumbycolour formula?
This function works propertly only the first time i write the formula sumbycolour(cellcolour;sumrange).
Thank you all
Function SUMBYCOLOUR(CellColour As Range, SumRange As Range)
Dim cell As Range
Dim SumColour As Integer
Dim MySum
SumColour = CellColour.Interior.ColorIndex
For Each cell In SumRange
If cell.Interior.ColorIndex = SumColour Then
MySum = 1 + MySum
End If
Next cell
SUMBYCOLOUR = MySum
End Function
Posted by Ivan F Moala on August 24, 2001 2:48 PM
Try putting in
Application.Volatile
@ the beginning of your function
Ivan
Posted by cpod on August 24, 2001 3:59 PM
I order to recalculate a sum based on color you have to do a full calc - Control+Shift+Enter
Posted by cpod on August 24, 2001 4:01 PM
Sorry I meant Crl+Alt+Enter
Posted by cpod on August 24, 2001 4:18 PM
ok, I'm pretty sure this time
A full calc is Cnl+Alt+F9.
It's Friday, it been a long week.
Posted by arpan on August 25, 2001 6:50 AM
This works only with the pression of F9, i wish to do this automatically every time the colour cells changes, is it possible...THANK YOU
Posted by Ivan F Moala on August 25, 2001 1:31 PM
Your code is not updating automatically because
the function is dependent on a change in the cell
property and not a change in calculation. So you
will have to do this little work around to fire
an event change.
Note: Your function Will not work if the colour
change is done via Coditional formatting.
In the Worksheet_SelectionChange event
avil via
1) Right click sheet tab
2) select view code
paste this code in
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
[z1] = [z1]
End Sub
Also in your function make sure application.volatile is left in
Ivan
This works only with the pression of F9, i wish to do this automatically every time the colour cells changes, is it possible...THANK YOU
