ColorIndexOfOneCell
 

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

ColorIndexOfOneCell
 

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" 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

ColorIndexOfOneCell
 

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" 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

ColorIndexOfOneCell
 

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
...
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" 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

ColorIndexOfOneCell
 

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" wrote in message
...
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" 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

ColorIndexOfOneCell
 

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
FileImport 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" 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" wrote in message
...
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" 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

ColorIndexOfOneCell
 

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
...
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
FileImport 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" 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" wrote in message
...
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" 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

ColorIndexOfOneCell
 

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" 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
.. .
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
FileImport 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" 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" wrote in message
...
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" 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

ColorIndexOfOneCell
 

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
...
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" 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
. ..
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
FileImport 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"
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" wrote in message
...
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" 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