Checking ColorIndex in Excel 1

[wadjssd]

I have written a small function to check if a cell's interior color matches a color specified by the user. Here is the code:

Function ColorCheck(Rng As Range, Indx As Integer) As String
If Rng.Interior.ColorIndex = Indx Then
ColorCheck = "Yes"
Else
ColorCheck = "No"
End If
End Function

This works fine, but there is one small problem that I need to get around.....

Because the interior color of a cell is not part of the cell's contents, this function does not update when the color is changed. For example, if a cell is filled with red (ColorIndex = 3), and I use this index in the function, the result is "Yes". If I then change the cell's color to something else, the result stays as "Yes", even though it should now be "No". Pressing F9 to recalculate does not seem to do anything.
Does anyone have any ideas about how to get around this - I thought about an On_Change routine to go through all of the cells and pass the F2-enter keystrokes, but the user is going to have hundreds and hundreds of instances of this formula, and this would therefore be very inefficient.

wadjssd

 

[xlbo]

try using application = volatile as the 1st line of your function - this will recalc every time a calc occurs on the sheet
HTH
Geoff

 

[xlbo]

Ooops - I presume from the star that you figured it needs to be application.volatile NOT application = volatile

still too early
G

 

[wadjssd]

Yes ta!


wadjssd