I have tried to sort by color using a VBA. It was successful on one
spreadsheet I am working on, but not on another. I keep getting the error
message "NAME?", and I don't know why. Any help would be appreciated.

Thank you

hi
it would help if you posted your code.

regards
FSt1

"Churley" wrote:

I have tried to sort by color using a VBA. It was successful on one
spreadsheet I am working on, but not on another. I keep getting the error
message "NAME?", and I don't know why. Any help would be appreciated.

Thank you

This is the VBA I used:
Function ColorIndexOfCell(Rng As Range, _
Optional OfText As Boolean, _
Optional DefaultAsIndex As Boolean = True) As Integer

Dim C As Long
If OfText = True Then
C = Rng.Font.ColorIndex
Else
C = Rng.Interior.ColorIndex
End If

If (C < 0) And (DefaultAsIndex = True) Then
If OfText = True Then
C = GetBlack(Rng.Worksheet.Parent)
Else
C = GetWhite(Rng.Worksheet.Parent)
End If
End If

ColorIndexOfCell = C

End Function


Function GetWhite(WB As Workbook) As Long
Dim Ndx As Long
For Ndx = 1 To 56
If WB.Colors(Ndx) = &HFFFFFF Then
GetWhite = Ndx
Exit Function
End If
Next Ndx
GetWhite = 0
End Function

Function GetBlack(WB As Workbook) As Long
Dim Ndx As Long
For Ndx = 1 To 56
If WB.Colors(Ndx) = 0& Then
GetBlack = Ndx
Exit Function
End If
Next Ndx
GetBlack = 0
End Function


"FSt1" wrote:

hi
it would help if you posted your code.

regards
FSt1

"Churley" wrote:

I have tried to sort by color using a VBA. It was successful on one
spreadsheet I am working on, but not on another. I keep getting the error
message "NAME?", and I don't know why. Any help would be appreciated.

Thank you

hi
chip pearson's color functions.
where do you have these functions installed. unless they are installed in an
open workbook such as a personal.xls, they wont be available to all files. if
you have them installed in the other workbook that they worked in and not the
workbook you are working in now, that is why you're having problems.

Regards
FSt1

"Churley" wrote:

This is the VBA I used:
Function ColorIndexOfCell(Rng As Range, _
Optional OfText As Boolean, _
Optional DefaultAsIndex As Boolean = True) As Integer

Dim C As Long
If OfText = True Then
C = Rng.Font.ColorIndex
Else
C = Rng.Interior.ColorIndex
End If

If (C < 0) And (DefaultAsIndex = True) Then
If OfText = True Then
C = GetBlack(Rng.Worksheet.Parent)
Else
C = GetWhite(Rng.Worksheet.Parent)
End If
End If

ColorIndexOfCell = C

End Function


Function GetWhite(WB As Workbook) As Long
Dim Ndx As Long
For Ndx = 1 To 56
If WB.Colors(Ndx) = &HFFFFFF Then
GetWhite = Ndx
Exit Function
End If
Next Ndx
GetWhite = 0
End Function

Function GetBlack(WB As Workbook) As Long
Dim Ndx As Long
For Ndx = 1 To 56
If WB.Colors(Ndx) = 0& Then
GetBlack = Ndx
Exit Function
End If
Next Ndx
GetBlack = 0
End Function


"FSt1" wrote:

hi
it would help if you posted your code.

regards
FSt1

"Churley" wrote:

I have tried to sort by color using a VBA. It was successful on one
spreadsheet I am working on, but not on another. I keep getting the error
message "NAME?", and I don't know why. Any help would be appreciated.

Thank you

That looks like my code. You need to put all the code in a regular code
module (in VBA, Insert menu, Module), not the ThisWorkbook module and not
one of the Sheet modules. Once you do that, the code can be run by bringing
up the Macros dialog (ALT F8), selecting the procedure name in the list, and
clicking "Run". That will work for the workbook that contains the code. If
you want to use this code for any open workbook, you can put it in a regular
module in your Pearsonal.xls workbook. Then, in the Macros dialog, select
"All Open Workbooks" in the "Macros In" ComboBox.


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2008
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)


"Churley" wrote in message
...
This is the VBA I used:
Function ColorIndexOfCell(Rng As Range, _
Optional OfText As Boolean, _
Optional DefaultAsIndex As Boolean = True) As Integer

Dim C As Long
If OfText = True Then
C = Rng.Font.ColorIndex
Else
C = Rng.Interior.ColorIndex
End If

If (C < 0) And (DefaultAsIndex = True) Then
If OfText = True Then
C = GetBlack(Rng.Worksheet.Parent)
Else
C = GetWhite(Rng.Worksheet.Parent)
End If
End If

ColorIndexOfCell = C

End Function


Function GetWhite(WB As Workbook) As Long
Dim Ndx As Long
For Ndx = 1 To 56
If WB.Colors(Ndx) = &HFFFFFF Then
GetWhite = Ndx
Exit Function
End If
Next Ndx
GetWhite = 0
End Function

Function GetBlack(WB As Workbook) As Long
Dim Ndx As Long
For Ndx = 1 To 56
If WB.Colors(Ndx) = 0& Then
GetBlack = Ndx
Exit Function
End If
Next Ndx
GetBlack = 0
End Function


"FSt1" wrote:

hi
it would help if you posted your code.

regards
FSt1

"Churley" wrote:

I have tried to sort by color using a VBA. It was successful on one
spreadsheet I am working on, but not on another. I keep getting the
error
message "NAME?", and I don't know why. Any help would be appreciated.

Thank you

Answered in your post of 15 minutes ago.

Have a little patience..........15 minutes is not a long time to wait for a
reply.


Gord Dibben MS Excel MVP
On Fri, 15 Feb 2008 08:37:02 -0800, Churley
wrote:

I have tried to sort by color using a VBA. It was successful on one
spreadsheet I am working on, but not on another. I keep getting the error
message "NAME?", and I don't know why. Any help would be appreciated.

Thank you