Is there any code written or any way that anyone knows of
to sort or filter based on the font or fill color?

Thanks,
Dan

Hi Dan
to do this you need a custom function which would return the colorindex
of your cell in a helper column. Find below such a function:


To get the colorindex of a specific cell use
=ColorIndex(A1)
and copy this down. Now use column B as sorting/filtering criteria


------
'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



--
Regards
Frank Kabel
Frankfurt, Germany


Dan wrote:
Is there any code written or any way that anyone knows of
to sort or filter based on the font or fill color?

Thanks,
Dan

Dan,

You need a helper column with a UDF that calculates the colour, and sort on
that

Her is a sample UDF

Function ColorIndex(rng As Range, Optional Text as Boolean = False))
If Text Then
ColorIndex = rng.Font.ColorIndex
Else
ColorIndex = rng.Interior.ColorIndex
End If
End Function

Use like
=ColorIndex(A1) for the cell colour or
=ColorIndex(A1, True) for the text colour

and then sort by that helper column.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Dan" wrote in message
...
Is there any code written or any way that anyone knows of
to sort or filter based on the font or fill color?

Thanks,
Dan

Hi Bob
and I posted yours (and Harlans) full colorindex function and you just
gave the simplest version to the OP <vbg


--
Regards
Frank Kabel
Frankfurt, Germany


Bob Phillips wrote:
Dan,

You need a helper column with a UDF that calculates the colour, and
sort on that

Her is a sample UDF

Function ColorIndex(rng As Range, Optional Text as Boolean = False))
If Text Then
ColorIndex = rng.Font.ColorIndex
Else
ColorIndex = rng.Interior.ColorIndex
End If
End Function

Use like
=ColorIndex(A1) for the cell colour or
=ColorIndex(A1, True) for the text colour

and then sort by that helper column.


"Dan" wrote in message
...
Is there any code written or any way that anyone knows of
to sort or filter based on the font or fill color?

Thanks,
Dan

Hi Frank,

Keep posting it, it's good<vbg.

That function is better than my short version, in that it handles black and
white colours better than the short version, and it handles a multi-cell
range, which the short version doesn't. In the helper column, the multi-cell
range is not needed, but the black and white is probably useful still.

I need to produce a cut-down version to assist with sorting (actually I have
an add-in that automatically sorts by colour, names, IP addresses, and can
also do worksheets (custom or alphabetically), so it never seemed necessary.
Another project for the list.

Bob

"Frank Kabel" wrote in message
...
Hi Bob
and I posted yours (and Harlans) full colorindex function and you just
gave the simplest version to the OP <vbg


--
Regards
Frank Kabel
Frankfurt, Germany

Bob Phillips wrote:
Hi Frank,

Keep posting it, it's good<vbg.

Looking at the authors - no wonder <ebg



I need to produce a cut-down version to assist with sorting (actually
I have an add-in that automatically sorts by colour, names, IP
addresses, and can also do worksheets (custom or alphabetically), so
it never seemed necessary. Another project for the list.

What Add-in? (available somethere)

Only by mail, I don't have a public website (yet!).

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Frank Kabel" wrote in message
...
Bob Phillips wrote:
Hi Frank,

Keep posting it, it's good<vbg.

Looking at the authors - no wonder <ebg



I need to produce a cut-down version to assist with sorting (actually
I have an add-in that automatically sorts by colour, names, IP
addresses, and can also do worksheets (custom or alphabetically), so
it never seemed necessary. Another project for the list.

What Add-in? (available somethere)