Using the following function from...
http://www.cpearson.com/excel/colors.aspx

I [ALT] F11, right-click on Modules, Insert and pasted..

Function ColorIndexOfOneCell(Cell As Range, OfText As Boolean, _
DefaultColorIndex As Long) As Long
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' ColorIndexOfOneCell
' This returns the ColorIndex of the cell referenced by Cell.
' If Cell refers to more than one cell, only Cell(1,1) is
' tested. If OfText True, the ColorIndex of the Font property is
' returned. If OfText is False, the ColorIndex of the Interior
' property is returned. If DefaultColorIndex is >= 0, this
' value is returned if the ColorIndex is either xlColorIndexNone
' or xlColorIndexAutomatic.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim CI As Long

Application.Volatile True
If OfText = True Then
CI = Cell(1, 1).Font.ColorIndex
Else
CI = Cell(1, 1).Interior.ColorIndex
End If
If CI < 0 Then
If IsValidColorIndex(ColorIndex:=DefaultColorIndex) = True Then
CI = DefaultColorIndex
Else
CI = -1
End If
End If

ColorIndexOfOneCell = CI

End Function

....in A2, I put the formula =COLORINDEXOFONECELL(C1,FALSE,1)

Resulting error: Sub or Function not defined on line....
If IsValidColorIndex(ColorIndex:=DefaultColorIndex) = True Then
CI = DefaultColorIndex

....with IsValidColorIndex highlighted.

What's wrong?
thanks

IsValidColorIndex( ... ) is another function which is used by this
one, so you need to have pasted that one over to your workbook as
well. The error message is telling you that the function is missing.

Hope this helps.

Pete

On Aug 15, 7:07*pm, "shank" <sh...@tampabay.rr.com> wrote:
> Using the following function from...http://www.cpearson.com/excel/colors.aspx
>
> I [ALT] F11, right-click on Modules, Insert and pasted..
>
> Function ColorIndexOfOneCell(Cell As Range, OfText As Boolean, _
> * * * * DefaultColorIndex As Long) As Long
> * * '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
> * * ' ColorIndexOfOneCell
> * * ' This returns the ColorIndex of the cell referenced by Cell.
> * * ' If Cell refers to more than one cell, only Cell(1,1) is
> * * ' tested. If OfText True, the ColorIndex of the Font property is
> * * ' returned. If OfText is False, the ColorIndex of the Interior
> * * ' property is returned. If DefaultColorIndex is >= 0, this
> * * ' value is returned if the ColorIndex is either xlColorIndexNone
> * * ' or xlColorIndexAutomatic.
> * * '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
> * * Dim CI As Long
>
> * * Application.Volatile True
> * * If OfText = True Then
> * * * * CI = Cell(1, 1).Font.ColorIndex
> * * Else
> * * * * CI = Cell(1, 1).Interior.ColorIndex
> * * End If
> * * If CI < 0 Then
> * * * * If IsValidColorIndex(ColorIndex:=DefaultColorIndex) =True Then
> * * * * * * CI = DefaultColorIndex
> * * * * Else
> * * * * * * CI = -1
> * * * * End If
> * * End If
>
> * * ColorIndexOfOneCell = CI
>
> * * End Function
>
> ...in A2, I put the formula =COLORINDEXOFONECELL(C1,FALSE,1)
>
> Resulting error: Sub or Function not defined on line....
> If IsValidColorIndex(ColorIndex:=DefaultColorIndex) = True Then
> * * * * * * CI = DefaultColorIndex
>
> ...with IsValidColorIndex highlighted.
>
> What's wrong?
> thanks

Add a dot

If IsValid.ColorIndex(ColorIndex:=DefaultColorIndex) = True Then

Works for me.


Gord Dibben MS Excel MVP

On Fri, 15 Aug 2008 14:07:01 -0400, "shank" <(E-Mail Removed)> wrote:

>Using the following function from...
>http://www.cpearson.com/excel/colors.aspx
>
>I [ALT] F11, right-click on Modules, Insert and pasted..
>
>Function ColorIndexOfOneCell(Cell As Range, OfText As Boolean, _
> DefaultColorIndex As Long) As Long
> '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
> ' ColorIndexOfOneCell
> ' This returns the ColorIndex of the cell referenced by Cell.
> ' If Cell refers to more than one cell, only Cell(1,1) is
> ' tested. If OfText True, the ColorIndex of the Font property is
> ' returned. If OfText is False, the ColorIndex of the Interior
> ' property is returned. If DefaultColorIndex is >= 0, this
> ' value is returned if the ColorIndex is either xlColorIndexNone
> ' or xlColorIndexAutomatic.
> '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
> Dim CI As Long
>
> Application.Volatile True
> If OfText = True Then
> CI = Cell(1, 1).Font.ColorIndex
> Else
> CI = Cell(1, 1).Interior.ColorIndex
> End If
> If CI < 0 Then
> If IsValidColorIndex(ColorIndex:=DefaultColorIndex) = True Then
> CI = DefaultColorIndex
> Else
> CI = -1
> End If
> End If
>
> ColorIndexOfOneCell = CI
>
> End Function
>
>...in A2, I put the formula =COLORINDEXOFONECELL(C1,FALSE,1)
>
>Resulting error: Sub or Function not defined on line....
>If IsValidColorIndex(ColorIndex:=DefaultColorIndex) = True Then
> CI = DefaultColorIndex
>
>...with IsValidColorIndex highlighted.
>
>What's wrong?
>thanks
>
>
>

I added the dot and now I get #VALUE! No joy.
I deleted, saved, reopened, pasted code with dot back in.
Using =COLORINDEXOFONECELL(C2,FALSE,1) in cell A2
Still no joy. I don't get an error, just #NAME? in cell A2
thanks

"Gord Dibben" <gorddibbATshawDOTca> wrote in message
news:(E-Mail Removed)...
> Add a dot
>
> If IsValid.ColorIndex(ColorIndex:=DefaultColorIndex) = True Then
>
> Works for me.
>
>
> Gord Dibben MS Excel MVP
>
> On Fri, 15 Aug 2008 14:07:01 -0400, "shank" <(E-Mail Removed)> wrote:
>
>>Using the following function from...
>>http://www.cpearson.com/excel/colors.aspx
>>
>>I [ALT] F11, right-click on Modules, Insert and pasted..
>>
>>Function ColorIndexOfOneCell(Cell As Range, OfText As Boolean, _
>> DefaultColorIndex As Long) As Long
>> '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
>> ' ColorIndexOfOneCell
>> ' This returns the ColorIndex of the cell referenced by Cell.
>> ' If Cell refers to more than one cell, only Cell(1,1) is
>> ' tested. If OfText True, the ColorIndex of the Font property is
>> ' returned. If OfText is False, the ColorIndex of the Interior
>> ' property is returned. If DefaultColorIndex is >= 0, this
>> ' value is returned if the ColorIndex is either xlColorIndexNone
>> ' or xlColorIndexAutomatic.
>> '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
>> Dim CI As Long
>>
>> Application.Volatile True
>> If OfText = True Then
>> CI = Cell(1, 1).Font.ColorIndex
>> Else
>> CI = Cell(1, 1).Interior.ColorIndex
>> End If
>> If CI < 0 Then
>> If IsValidColorIndex(ColorIndex:=DefaultColorIndex) = True Then
>> CI = DefaultColorIndex
>> Else
>> CI = -1
>> End If
>> End If
>>
>> ColorIndexOfOneCell = CI
>>
>> End Function
>>
>>...in A2, I put the formula =COLORINDEXOFONECELL(C1,FALSE,1)
>>
>>Resulting error: Sub or Function not defined on line....
>>If IsValidColorIndex(ColorIndex:=DefaultColorIndex) = True Then
>> CI = DefaultColorIndex
>>
>>...with IsValidColorIndex highlighted.
>>
>>What's wrong?
>>thanks
>>
>>
>>
>

Not sure I understand. You're saying IsValidColorIndex( ... ) is a separate
function entirely?
I don't see that function pasted on the site. I would think if it's
necessary, it'd be there.
thanks!

"Pete_UK" <(E-Mail Removed)> wrote in message
news:e9597527-55ac-4b21-b034-(E-Mail Removed)...
IsValidColorIndex( ... ) is another function which is used by this
one, so you need to have pasted that one over to your workbook as
well. The error message is telling you that the function is missing.

Hope this helps.

Pete

On Aug 15, 7:07 pm, "shank" <sh...@tampabay.rr.com> wrote:
> Using the following function
> from...http://www.cpearson.com/excel/colors.aspx
>
> I [ALT] F11, right-click on Modules, Insert and pasted..
>
> Function ColorIndexOfOneCell(Cell As Range, OfText As Boolean, _
> DefaultColorIndex As Long) As Long
> '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
> ' ColorIndexOfOneCell
> ' This returns the ColorIndex of the cell referenced by Cell.
> ' If Cell refers to more than one cell, only Cell(1,1) is
> ' tested. If OfText True, the ColorIndex of the Font property is
> ' returned. If OfText is False, the ColorIndex of the Interior
> ' property is returned. If DefaultColorIndex is >= 0, this
> ' value is returned if the ColorIndex is either xlColorIndexNone
> ' or xlColorIndexAutomatic.
> '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
> Dim CI As Long
>
> Application.Volatile True
> If OfText = True Then
> CI = Cell(1, 1).Font.ColorIndex
> Else
> CI = Cell(1, 1).Interior.ColorIndex
> End If
> If CI < 0 Then
> If IsValidColorIndex(ColorIndex:=DefaultColorIndex) = True Then
> CI = DefaultColorIndex
> Else
> CI = -1
> End If
> End If
>
> ColorIndexOfOneCell = CI
>
> End Function
>
> ...in A2, I put the formula =COLORINDEXOFONECELL(C1,FALSE,1)
>
> Resulting error: Sub or Function not defined on line....
> If IsValidColorIndex(ColorIndex:=DefaultColorIndex) = True Then
> CI = DefaultColorIndex
>
> ...with IsValidColorIndex highlighted.
>
> What's wrong?
> thanks

My mistake............Pete is correct, although coincidentally the "add a
dot" works in this case.

The function is included in the modColorFunction.bas download file

You would download the *.bas file then import to your workbook using
File>Import File or right-click on your current project and Import File.

Or just add this function to your workbook.

Private Function IsValidColorIndex(ColorIndex As Long) As Boolean
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' IsValidColorIndex
' This returns TRUE if ColorIndex is between 1 and 56 or equal
' to either xlColorIndexNone or xlColorIndexAutomatic. It
' returns FALSE otherwise.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Select Case ColorIndex
Case 1 To 56, xlColorIndexNone, xlColorIndexAutomatic
IsValidColorIndex = True
Case Else
IsValidColorIndex = False
End Select
End Function


Gord

On Fri, 15 Aug 2008 14:42:54 -0400, "shank" <(E-Mail Removed)> wrote:

>Not sure I understand. You're saying IsValidColorIndex( ... ) is a separate
>function entirely?
>I don't see that function pasted on the site. I would think if it's
>necessary, it'd be there.
>thanks!
>
>"Pete_UK" <(E-Mail Removed)> wrote in message
>news:e9597527-55ac-4b21-b034-(E-Mail Removed)...
>IsValidColorIndex( ... ) is another function which is used by this
>one, so you need to have pasted that one over to your workbook as
>well. The error message is telling you that the function is missing.
>
>Hope this helps.
>
>Pete
>
>On Aug 15, 7:07 pm, "shank" <sh...@tampabay.rr.com> wrote:
>> Using the following function
>> from...http://www.cpearson.com/excel/colors.aspx
>>
>> I [ALT] F11, right-click on Modules, Insert and pasted..
>>
>> Function ColorIndexOfOneCell(Cell As Range, OfText As Boolean, _
>> DefaultColorIndex As Long) As Long
>> '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
>> ' ColorIndexOfOneCell
>> ' This returns the ColorIndex of the cell referenced by Cell.
>> ' If Cell refers to more than one cell, only Cell(1,1) is
>> ' tested. If OfText True, the ColorIndex of the Font property is
>> ' returned. If OfText is False, the ColorIndex of the Interior
>> ' property is returned. If DefaultColorIndex is >= 0, this
>> ' value is returned if the ColorIndex is either xlColorIndexNone
>> ' or xlColorIndexAutomatic.
>> '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
>> Dim CI As Long
>>
>> Application.Volatile True
>> If OfText = True Then
>> CI = Cell(1, 1).Font.ColorIndex
>> Else
>> CI = Cell(1, 1).Interior.ColorIndex
>> End If
>> If CI < 0 Then
>> If IsValidColorIndex(ColorIndex:=DefaultColorIndex) = True Then
>> CI = DefaultColorIndex
>> Else
>> CI = -1
>> End If
>> End If
>>
>> ColorIndexOfOneCell = CI
>>
>> End Function
>>
>> ...in A2, I put the formula =COLORINDEXOFONECELL(C1,FALSE,1)
>>
>> Resulting error: Sub or Function not defined on line....
>> If IsValidColorIndex(ColorIndex:=DefaultColorIndex) = True Then
>> CI = DefaultColorIndex
>>
>> ...with IsValidColorIndex highlighted.
>>
>> What's wrong?
>> thanks
>

I added the below - no joy.
I deleted all, reopened, then imported the *.bas file.
Same difference. I just get #NAME!

Would it make any difference if I have Excel 2007?

thanks

"Gord Dibben" <gorddibbATshawDOTca> wrote in message
news:(E-Mail Removed)...
> My mistake............Pete is correct, although coincidentally the "add a
> dot" works in this case.
>
> The function is included in the modColorFunction.bas download file
>
> You would download the *.bas file then import to your workbook using
> File>Import File or right-click on your current project and Import File.
>
> Or just add this function to your workbook.
>
> Private Function IsValidColorIndex(ColorIndex As Long) As Boolean
> '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
> ' IsValidColorIndex
> ' This returns TRUE if ColorIndex is between 1 and 56 or equal
> ' to either xlColorIndexNone or xlColorIndexAutomatic. It
> ' returns FALSE otherwise.
> '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
> Select Case ColorIndex
> Case 1 To 56, xlColorIndexNone, xlColorIndexAutomatic
> IsValidColorIndex = True
> Case Else
> IsValidColorIndex = False
> End Select
> End Function
>
>
> Gord
>
> On Fri, 15 Aug 2008 14:42:54 -0400, "shank" <(E-Mail Removed)> wrote:
>
>>Not sure I understand. You're saying IsValidColorIndex( ... ) is a
>>separate
>>function entirely?
>>I don't see that function pasted on the site. I would think if it's
>>necessary, it'd be there.
>>thanks!
>>
>>"Pete_UK" <(E-Mail Removed)> wrote in message
>>news:e9597527-55ac-4b21-b034-(E-Mail Removed)...
>>IsValidColorIndex( ... ) is another function which is used by this
>>one, so you need to have pasted that one over to your workbook as
>>well. The error message is telling you that the function is missing.
>>
>>Hope this helps.
>>
>>Pete
>>
>>On Aug 15, 7:07 pm, "shank" <sh...@tampabay.rr.com> wrote:
>>> Using the following function
>>> from...http://www.cpearson.com/excel/colors.aspx
>>>
>>> I [ALT] F11, right-click on Modules, Insert and pasted..
>>>
>>> Function ColorIndexOfOneCell(Cell As Range, OfText As Boolean, _
>>> DefaultColorIndex As Long) As Long
>>> '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
>>> ' ColorIndexOfOneCell
>>> ' This returns the ColorIndex of the cell referenced by Cell.
>>> ' If Cell refers to more than one cell, only Cell(1,1) is
>>> ' tested. If OfText True, the ColorIndex of the Font property is
>>> ' returned. If OfText is False, the ColorIndex of the Interior
>>> ' property is returned. If DefaultColorIndex is >= 0, this
>>> ' value is returned if the ColorIndex is either xlColorIndexNone
>>> ' or xlColorIndexAutomatic.
>>> '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
>>> Dim CI As Long
>>>
>>> Application.Volatile True
>>> If OfText = True Then
>>> CI = Cell(1, 1).Font.ColorIndex
>>> Else
>>> CI = Cell(1, 1).Interior.ColorIndex
>>> End If
>>> If CI < 0 Then
>>> If IsValidColorIndex(ColorIndex:=DefaultColorIndex) = True Then
>>> CI = DefaultColorIndex
>>> Else
>>> CI = -1
>>> End If
>>> End If
>>>
>>> ColorIndexOfOneCell = CI
>>>
>>> End Function
>>>
>>> ...in A2, I put the formula =COLORINDEXOFONECELL(C1,FALSE,1)
>>>
>>> Resulting error: Sub or Function not defined on line....
>>> If IsValidColorIndex(ColorIndex:=DefaultColorIndex) = True Then
>>> CI = DefaultColorIndex
>>>
>>> ...with IsValidColorIndex highlighted.
>>>
>>> What's wrong?
>>> thanks
>>
>

Where did you store the Functions?

They belong in a General Module in your workbook.

If you imported the *.bas file all the functions should be in a module named

modcolorfunctions. Do you have that?

The #NAME! error indicates Excel can't find the functions.

These functions are not version-dependent because they are not built-in
functions.


Gord


On Fri, 15 Aug 2008 16:20:46 -0400, "shank" <(E-Mail Removed)> wrote:

>I added the below - no joy.
>I deleted all, reopened, then imported the *.bas file.
>Same difference. I just get #NAME!
>
>Would it make any difference if I have Excel 2007?
>
>thanks
>
>"Gord Dibben" <gorddibbATshawDOTca> wrote in message
>news:(E-Mail Removed)...
>> My mistake............Pete is correct, although coincidentally the "add a
>> dot" works in this case.
>>
>> The function is included in the modColorFunction.bas download file
>>
>> You would download the *.bas file then import to your workbook using
>> File>Import File or right-click on your current project and Import File.
>>
>> Or just add this function to your workbook.
>>
>> Private Function IsValidColorIndex(ColorIndex As Long) As Boolean
>> '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
>> ' IsValidColorIndex
>> ' This returns TRUE if ColorIndex is between 1 and 56 or equal
>> ' to either xlColorIndexNone or xlColorIndexAutomatic. It
>> ' returns FALSE otherwise.
>> '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
>> Select Case ColorIndex
>> Case 1 To 56, xlColorIndexNone, xlColorIndexAutomatic
>> IsValidColorIndex = True
>> Case Else
>> IsValidColorIndex = False
>> End Select
>> End Function
>>
>>
>> Gord
>>
>> On Fri, 15 Aug 2008 14:42:54 -0400, "shank" <(E-Mail Removed)> wrote:
>>
>>>Not sure I understand. You're saying IsValidColorIndex( ... ) is a
>>>separate
>>>function entirely?
>>>I don't see that function pasted on the site. I would think if it's
>>>necessary, it'd be there.
>>>thanks!
>>>
>>>"Pete_UK" <(E-Mail Removed)> wrote in message
>>>news:e9597527-55ac-4b21-b034-(E-Mail Removed)...
>>>IsValidColorIndex( ... ) is another function which is used by this
>>>one, so you need to have pasted that one over to your workbook as
>>>well. The error message is telling you that the function is missing.
>>>
>>>Hope this helps.
>>>
>>>Pete
>>>
>>>On Aug 15, 7:07 pm, "shank" <sh...@tampabay.rr.com> wrote:
>>>> Using the following function
>>>> from...http://www.cpearson.com/excel/colors.aspx
>>>>
>>>> I [ALT] F11, right-click on Modules, Insert and pasted..
>>>>
>>>> Function ColorIndexOfOneCell(Cell As Range, OfText As Boolean, _
>>>> DefaultColorIndex As Long) As Long
>>>> '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
>>>> ' ColorIndexOfOneCell
>>>> ' This returns the ColorIndex of the cell referenced by Cell.
>>>> ' If Cell refers to more than one cell, only Cell(1,1) is
>>>> ' tested. If OfText True, the ColorIndex of the Font property is
>>>> ' returned. If OfText is False, the ColorIndex of the Interior
>>>> ' property is returned. If DefaultColorIndex is >= 0, this
>>>> ' value is returned if the ColorIndex is either xlColorIndexNone
>>>> ' or xlColorIndexAutomatic.
>>>> '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
>>>> Dim CI As Long
>>>>
>>>> Application.Volatile True
>>>> If OfText = True Then
>>>> CI = Cell(1, 1).Font.ColorIndex
>>>> Else
>>>> CI = Cell(1, 1).Interior.ColorIndex
>>>> End If
>>>> If CI < 0 Then
>>>> If IsValidColorIndex(ColorIndex:=DefaultColorIndex) = True Then
>>>> CI = DefaultColorIndex
>>>> Else
>>>> CI = -1
>>>> End If
>>>> End If
>>>>
>>>> ColorIndexOfOneCell = CI
>>>>
>>>> End Function
>>>>
>>>> ...in A2, I put the formula =COLORINDEXOFONECELL(C1,FALSE,1)
>>>>
>>>> Resulting error: Sub or Function not defined on line....
>>>> If IsValidColorIndex(ColorIndex:=DefaultColorIndex) = True Then
>>>> CI = DefaultColorIndex
>>>>
>>>> ...with IsValidColorIndex highlighted.
>>>>
>>>> What's wrong?
>>>> thanks
>>>
>>
>

I gave up on this and did my work manually. Just got back to the module
today. I started a new session, new workbook, new sheet... no problems!
Works fine. Just something with that workbook I guess. I imported and used
same formula as before. Go figure.
thanks

"Gord Dibben" <gorddibbATshawDOTca> wrote in message
news(E-Mail Removed)...
> Where did you store the Functions?
>
> They belong in a General Module in your workbook.
>
> If you imported the *.bas file all the functions should be in a module
> named
>
> modcolorfunctions. Do you have that?
>
> The #NAME! error indicates Excel can't find the functions.
>
> These functions are not version-dependent because they are not built-in
> functions.
>
>
> Gord
>
>
> On Fri, 15 Aug 2008 16:20:46 -0400, "shank" <(E-Mail Removed)> wrote:
>
>>I added the below - no joy.
>>I deleted all, reopened, then imported the *.bas file.
>>Same difference. I just get #NAME!
>>
>>Would it make any difference if I have Excel 2007?
>>
>>thanks
>>
>>"Gord Dibben" <gorddibbATshawDOTca> wrote in message
>>news:(E-Mail Removed)...
>>> My mistake............Pete is correct, although coincidentally the "add
>>> a
>>> dot" works in this case.
>>>
>>> The function is included in the modColorFunction.bas download file
>>>
>>> You would download the *.bas file then import to your workbook using
>>> File>Import File or right-click on your current project and Import
>>> File.
>>>
>>> Or just add this function to your workbook.
>>>
>>> Private Function IsValidColorIndex(ColorIndex As Long) As Boolean
>>> '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
>>> ' IsValidColorIndex
>>> ' This returns TRUE if ColorIndex is between 1 and 56 or equal
>>> ' to either xlColorIndexNone or xlColorIndexAutomatic. It
>>> ' returns FALSE otherwise.
>>> '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
>>> Select Case ColorIndex
>>> Case 1 To 56, xlColorIndexNone, xlColorIndexAutomatic
>>> IsValidColorIndex = True
>>> Case Else
>>> IsValidColorIndex = False
>>> End Select
>>> End Function
>>>
>>>
>>> Gord
>>>
>>> On Fri, 15 Aug 2008 14:42:54 -0400, "shank" <(E-Mail Removed)>
>>> wrote:
>>>
>>>>Not sure I understand. You're saying IsValidColorIndex( ... ) is a
>>>>separate
>>>>function entirely?
>>>>I don't see that function pasted on the site. I would think if it's
>>>>necessary, it'd be there.
>>>>thanks!
>>>>
>>>>"Pete_UK" <(E-Mail Removed)> wrote in message
>>>>news:e9597527-55ac-4b21-b034-(E-Mail Removed)...
>>>>IsValidColorIndex( ... ) is another function which is used by this
>>>>one, so you need to have pasted that one over to your workbook as
>>>>well. The error message is telling you that the function is missing.
>>>>
>>>>Hope this helps.
>>>>
>>>>Pete
>>>>
>>>>On Aug 15, 7:07 pm, "shank" <sh...@tampabay.rr.com> wrote:
>>>>> Using the following function
>>>>> from...http://www.cpearson.com/excel/colors.aspx
>>>>>
>>>>> I [ALT] F11, right-click on Modules, Insert and pasted..
>>>>>
>>>>> Function ColorIndexOfOneCell(Cell As Range, OfText As Boolean, _
>>>>> DefaultColorIndex As Long) As Long
>>>>> '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
>>>>> ' ColorIndexOfOneCell
>>>>> ' This returns the ColorIndex of the cell referenced by Cell.
>>>>> ' If Cell refers to more than one cell, only Cell(1,1) is
>>>>> ' tested. If OfText True, the ColorIndex of the Font property is
>>>>> ' returned. If OfText is False, the ColorIndex of the Interior
>>>>> ' property is returned. If DefaultColorIndex is >= 0, this
>>>>> ' value is returned if the ColorIndex is either xlColorIndexNone
>>>>> ' or xlColorIndexAutomatic.
>>>>> '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
>>>>> Dim CI As Long
>>>>>
>>>>> Application.Volatile True
>>>>> If OfText = True Then
>>>>> CI = Cell(1, 1).Font.ColorIndex
>>>>> Else
>>>>> CI = Cell(1, 1).Interior.ColorIndex
>>>>> End If
>>>>> If CI < 0 Then
>>>>> If IsValidColorIndex(ColorIndex:=DefaultColorIndex) = True Then
>>>>> CI = DefaultColorIndex
>>>>> Else
>>>>> CI = -1
>>>>> End If
>>>>> End If
>>>>>
>>>>> ColorIndexOfOneCell = CI
>>>>>
>>>>> End Function
>>>>>
>>>>> ...in A2, I put the formula =COLORINDEXOFONECELL(C1,FALSE,1)
>>>>>
>>>>> Resulting error: Sub or Function not defined on line....
>>>>> If IsValidColorIndex(ColorIndex:=DefaultColorIndex) = True Then
>>>>> CI = DefaultColorIndex
>>>>>
>>>>> ...with IsValidColorIndex highlighted.
>>>>>
>>>>> What's wrong?
>>>>> thanks
>>>>
>>>
>>
>