Problem with UDF Function

[YasserKhalil]

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

[xld]

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]

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 

VBA tags courtesy of www.thecodenet.com

Mark

[GTO]

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