I am trying to sort rows by color. I have created a column for ColorIndex and
then created the following VBA code.

Function ColorIndexOfCell(Rng As Range, Optional OfText As Boolean, Optional
DefaultAsIndex As Boolean = True) As Integar

Dim C As Long
If OfText = True Then
C = Range("c1:c10").Font.ColorIndex
Else
C = Range("c1:c10").Interior.ColorIndex
End If

If (C < 0) And (DefaultAsIndex = True) Then
If OfText = True Then
C = GetBlack(Range("c1:c10").Worksheet.Parent)
Else
C = GetWhite(Range("c1:c10").Worksheet.Parent)
End If
End If

ColorIndexOfCell = C

End Function

When I put the following formula into a cell of the Colorindex column

=COLORINDEXOFCELL(A1,TRUE,TRUE)

I get a compile error message "User-defined type not defined". Hopefully
it's a simple error, I am fairly new to VBA programming.

--
Marshall

Hi Marshall,

I get a compile error message "User-defined type not defined". Hopefully
it's a simple error, I am fairly new to VBA programming.

Try changing

Function ColorIndexOfCell(Rng As Range, Optional OfText As Boolean,
Optional
DefaultAsIndex As Boolean = True) As Integar

to

Function ColorIndexOfCell(Rng As Range, _
Optional OfText As Boolean, _
Optional DefaultAsIndex As Boolean = True) As Integer

(Integar == Integer).

There are, however, other proble,s with your function; no least that you do
not use the rng argument in the function and, instead use a hardcoded range.

For an alternative implementation, see xlDynamic at:

http://www.xldynamic.com/source/xld....r.html#sorting


---
Regards,
Norman



"Marshall Scmidt" wrote in
message ...
I am trying to sort rows by color. I have created a column for ColorIndex
and
then created the following VBA code.

Function ColorIndexOfCell(Rng As Range, Optional OfText As Boolean,
Optional
DefaultAsIndex As Boolean = True) As Integar

Dim C As Long
If OfText = True Then
C = Range("c1:c10").Font.ColorIndex
Else
C = Range("c1:c10").Interior.ColorIndex
End If

If (C < 0) And (DefaultAsIndex = True) Then
If OfText = True Then
C = GetBlack(Range("c1:c10").Worksheet.Parent)
Else
C = GetWhite(Range("c1:c10").Worksheet.Parent)
End If
End If

ColorIndexOfCell = C

End Function

When I put the following formula into a cell of the Colorindex column

=COLORINDEXOFCELL(A1,TRUE,TRUE)

I get a compile error message "User-defined type not defined". Hopefully
it's a simple error, I am fairly new to VBA programming.

--
Marshall

Hi Norman,

Many thanks for your help. I've just accessed the site you suggested.
--
Marshall


"Norman Jones" wrote:

Hi Marshall,

I get a compile error message "User-defined type not defined". Hopefully
it's a simple error, I am fairly new to VBA programming.

Try changing

Function ColorIndexOfCell(Rng As Range, Optional OfText As Boolean,
Optional
DefaultAsIndex As Boolean = True) As Integar

to

Function ColorIndexOfCell(Rng As Range, _
Optional OfText As Boolean, _
Optional DefaultAsIndex As Boolean = True) As Integer

(Integar == Integer).

There are, however, other proble,s with your function; no least that you do
not use the rng argument in the function and, instead use a hardcoded range.

For an alternative implementation, see xlDynamic at:

http://www.xldynamic.com/source/xld....r.html#sorting


---
Regards,
Norman



"Marshall Scmidt" wrote in
message ...
I am trying to sort rows by color. I have created a column for ColorIndex
and
then created the following VBA code.

Function ColorIndexOfCell(Rng As Range, Optional OfText As Boolean,
Optional
DefaultAsIndex As Boolean = True) As Integar

Dim C As Long
If OfText = True Then
C = Range("c1:c10").Font.ColorIndex
Else
C = Range("c1:c10").Interior.ColorIndex
End If

If (C < 0) And (DefaultAsIndex = True) Then
If OfText = True Then
C = GetBlack(Range("c1:c10").Worksheet.Parent)
Else
C = GetWhite(Range("c1:c10").Worksheet.Parent)
End If
End If

ColorIndexOfCell = C

End Function

When I put the following formula into a cell of the Colorindex column

=COLORINDEXOFCELL(A1,TRUE,TRUE)

I get a compile error message "User-defined type not defined". Hopefully
it's a simple error, I am fairly new to VBA programming.

--
Marshall

Hi, your problem is probably caused by your function line that ends wit
the word Integar but should be Integer.

Regards,
Simo

--
Simon Lloy
-----------------------------------------------------------------------
Simon Lloyd's Profile: http://www.excelforum.com/member.php...nfo&userid=670
View this thread: http://www.excelforum.com/showthread.php?threadid=57170