Just to update you guys...I put in a error handler and I think the
problem is solved. Thanks for the discussion.

crl

VBAnewbie wrote:
Yeah, the cells that are inspected for color are included in the
argument list of the function. And I use the Volatile method, which I
thought would make sure the cells are recalculated.

Here is the function:
Function CellColorIndex(inRange As Range, Optional ofText As Boolean =
False) As Integer

Application.Volatile
If ofText = True Then
CellColorIndex = inRange(1, 1).Font.ColorIndex
Else
CellColorIndex = inRange(1, 1).Interior.ColorIndex
End If

End Function

So I would change the color on cell B23 inside of a macro, then use
Calculate inside the macro. The cell that contains the formula
'=CellColorIndex(B23)' would now return a #VALUE, or also sometimes
#NAME. All I have to do is click on the cell and press enter and the
formula works again.



Niek Otten wrote:
Are all the cells that are inspected for color included in the argument list of the function call?
BTW, changing colors of cells does not trigger a recalculation.
So, it depends on the code of your function and the action you take which you expect to recalculate the function call.
An example maybe?

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"VBAnewbie" wrote in message oups.com...
|I have spreadsheet that hinges around counting colored cells. I used a
| user-defined CellColorIndex function to do this. It works great 90% of
| the time. I use it in another spreadsheet that is dependent on macros
| to move a bunch of information around. For some reason, about every
| tenth time i run the macro, I get a value error in the cells where I
| use the CellColorIndex function. To fix the error, all I have to do is
| click on the offending cell and hit return. Without changing the
| formula at all, the UDF works perfectly again. Has anyone else had
| this problem? Does anyone know how to fix the problem?
|