Function not Recognized

[Ken]

I am trying to use Chip Pearson's function to return the
color index of a cell:

Function CellColorIndex(InRange As Range, Optional _
OfText As Boolean = False) As Integer
'
' This function returns the ColorIndex value of a the
Interior
' (background) of a cell, or, if OfText is true, of the
Font in the cell.
'
Application.Volatile True
If OfText = True Then
CellColorIndex = InRange(1,1).Font.ColorIndex
Else
CellColorIndex = InRange(1,1).Interior.ColorIndex
End If

End Function

I have pasted the code into Sheet1.

In cell A1 I have set the background color to yellow.
In cell A2 I am trying to call the function with:

=CellColorIndex(A1,False)

I get the #NAME? error in A2. Looks to me like Excel is
not finding the function. Any ideas what I am doing wrong?

TIA.

 

[Juan Sanchez]

Function has to be pasted or written on a module, hit
Alt+F11, insert module and then paste the code in the code
area...

If your not familiar with UDF's see David McRicthie's
Getting Started With Macros and User Defined Functions @
www.mvps.org/dmcritchie/excel/getstarted.htm

Cheers
Juan

 

[Robin Hammond]

Ken,

In the VBE, insert a module and place the code in there rather than in the
code sheet for Sheet1. It should work then.

Robin Hammond
www.enhanceddatasystems.com