ColorIndexOfOneCell

S

shank

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
 
P

Pete_UK

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
 
G

Gord Dibben

Add a dot

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

Works for me.


Gord Dibben MS Excel MVP
 
S

shank

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 said:
Add a dot

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

Works for me.


Gord Dibben MS Excel MVP

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
Click to expand...
Click to expand...
 
S

shank

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!

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
 
G

Gord Dibben

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

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!

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

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
Click to expand...
Click to expand...
 
S

shank

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 said:
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

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!

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

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
Click to expand...
Click to expand...
Click to expand...
 
G

Gord Dibben

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


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 said:
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

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!

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

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
Click to expand...
Click to expand...
Click to expand...
 
S

shank

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 said:
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


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 said:
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

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!

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

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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help using Chip Pearsons code to count cells with color 9
count cells with diagonal borders 3
Word macro that can call cells out of Excel for a find and replace 0
count cells with diagonal cell borders 0
Array with multiple selection criteria 9
VBA-Table Word 2019 - Move a selected text, from a cell to the adjacent cell to its right, by pressing the ENTER key 0
Making a macro only work on certain files 2
Formula until condition met 4

Top