CellColorIndex - not working

[Malcom Jazz]

I tried to create the cell colour index function but it
gives #NAME? error.
Function CCOL(cell As Range) As Integer
Application.Volatile = True
CCOL = cell.Interior.ColorIndex.Value
End Function

Then I searched and copied function from the Chip's page
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

This also gives the same error.

It means there is an obvious error or some setting needs
to be changed on my Excel. I am using Excel 2000.

Can somebody tell me whats wrong with it?

Thanks,
Malcom

 

[Frank Kabel]

Hi
ythe added .value is the cause for this error. Try:
Function CCOL(cell As Range) As Integer
Application.Volatile = True
CCOL = cell.Interior.ColorIndex
End Function

Also put this code in a standard module and nOT in a worksheet module

 

[Bob Flanagan]

Malcom, try:

Function CCOL(cell As Range) As Integer
Application.Volatile
CCOL = cell.Interior.ColorIndex
End Function

Also, suggest you not use Application.Volatile unless you really need it.
If you are using the above function on a large number of cells, it has to
recalc every time you change a cell. Easier is to just to proess
ALT-CTL-F9, which forces a recalc when you need it.

Robert Flanagan
Macro Systems
Delaware, U.S. 302-234-9857
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel