SORTING BY COLOR

[Guest]

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.

 

[Norman Jones]

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.ColourCounter.html#sorting

 

[Guest]

Hi Norman,

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

 

[Simon Lloyd]

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

Regards,
Simo