PDA

View Full Version : Problem with UDF Function



YasserKhalil
07-30-2010, 02:34 AM
Hi guys
I got a UDF Function which counts the colored cells
It works fine
But when I try to color a cell within the range, Excel doesn't recalculate the colored cells until I clicked twice in the cell that has the function
I want a solution to get the result as soon as I color another cell within the range..


Function CountColors(myrng As Range)
Dim Cell As Range
For Each Cell In myrng
If Cell.Interior.ColorIndex <> xlNone Then
CountColors = CountColors + 1
End If
Next
End Function

Bob Phillips
07-30-2010, 03:22 AM
Not a hope dear boy. Colouring a cell does not trigger the Excel calculation, so your UDF does not fire.

You could add Application.Volatile which will cause your UDF to fire whenever any change that causes a sheet recalculation happens, but thgis will stilnot happen if a cell colour is changed.

GTO
07-30-2010, 03:23 AM
Changing colors is not recognized as a change, such as a change in value, nor will it cause a re-calculation of the sheet.

You could try using the selection change event to force a calc.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Application.Intersect(Target, Me.Range("A1:C15")) Is Nothing Then
Me.Calculate
End If
End Sub

Mark

GTO
07-30-2010, 03:51 AM
Here's a sharper example of what I was thinking might be a semi-okay workaround.

http://www.cpearson.com/excel/Colors.aspx

About 3/8'ths down the page. I am not saying I'd want this in anything but a small (calcualtion-wise) wb.

Hope that helps,

Mark