Can I sort a column by its 'formatting'...

S

steve

i have a column of values, some of the cells are in black
and some are in red.

can i sort the column let say by the color first, then the
numeric value.

example,

A
---- cell format
6 (black)
9 (red)
1 (red)
5 (black)
10 (black)

after sorting by 'color' then 'value'

A
----
1 (red)
9 (red)
5 (black)
6 (black)
10 (black)

is it possible? thx.
 
O

Otto Moehrbach

I don't think you can do that directly. You could put formulas in a
neighboring column that reference the condition that produced the colors,
then sort by that column. Or you can use VBA to fill in this extra column,
then sort by it. HTH Otto
 
F

Frank Kabel

Hi Steve
you'll need VBA for this. Posting again Bob Phillips and Harlan Grove's
function you may use the following:

------
using a procedure from Bob Phillips and Harlan Grove you may try the
following:
If column A stores your values enter the following in B1:
=ColorIndex(A1)
copy this down for all rows and sort with this helper column

------
'Code to paste in one of your modules

'---------------------------------------------------------------------
Function ColorIndex(rng As Range, _
Optional text As Boolean = False) As Variant
'---------------------------------------------------------------------
' Function: Returns the colorindex of the supplied range
' Synopsis:
' Author: Bob Phillips/Harlan Grove
'
'---------------------------------------------------------------------
Dim cell As Range, row As Range
Dim i As Long, j As Long
Dim iWhite As Long, iBlack As Long
Dim aryColours As Variant

If rng.Areas.Count > 1 Then
ColorIndex = CVErr(xlErrValue)
Exit Function
End If

iWhite = WhiteColorindex(rng.Worksheet.Parent)
iBlack = BlackColorindex(rng.Worksheet.Parent)

If rng.Cells.Count = 1 Then
If text Then
aryColours = DecodeColorIndex(rng, True, iBlack)
Else
aryColours = DecodeColorIndex(rng, False, iWhite)
End If

Else
aryColours = rng.Value
i = 0

For Each row In rng.Rows
i = i + 1
j = 0

For Each cell In row.Cells
j = j + 1

If text Then
aryColours(i, j) = _
DecodeColorIndex(cell,True,iBlack)
Else
aryColours(i, j) = _
DecodeColorIndex(cell,False,iWhite)
End If

Next cell

Next row

End If

ColorIndex = aryColours

End Function

Private Function WhiteColorindex(oWB As Workbook)
Dim iPalette As Long
WhiteColorindex = 0
For iPalette = 1 To 56
If oWB.Colors(iPalette) = &HFFFFFF Then
WhiteColorindex = iPalette
Exit Function
End If
Next iPalette
End Function

Private Function BlackColorindex(oWB As Workbook)
Dim iPalette As Long
BlackColorindex = 0
For iPalette = 1 To 56
If oWB.Colors(iPalette) = &H0 Then
BlackColorindex = iPalette
Exit Function
End If
Next iPalette
End Function

Private Function DecodeColorIndex(rng As Range, text As Boolean, idx As
_
Long)
Dim iColor As Long
If text Then
iColor = rng.font.ColorIndex
Else
iColor = rng.Interior.ColorIndex
End If
If iColor < 0 Then
iColor = idx
End If
DecodeColorIndex = iColor
End Function
 
Top