Filter by color

[Guest]

Hi,

I have a large spreadsheet. Many of the cells are highlighted with different
colors. I want to filter the database according to color like select the
cells with red background color only.
Is it possible?
Thank You in advance.


Regards,
Pawan

 

[Guest]

I have a large spreadsheet. Many of the cells are highlighted with different
colors. I want to filter the database according to color like select the
cells with red background color only.
Is it possible?

In place filtering can be a bit complex to achieve, an easier way is to copy
the values to another worksheet. For example if you have two columns of data
and you want to extract the rows where cells in col 2 have red background
color.

Private Sub CommandButton1_Click()
Dim j As Integer, row As Integer
row = 1
For j = 1 To 100
If Cells(j, 2).Interior.Color = vbRed Then
Worksheets("Sheet2").Cells(row, 1).Formula = Cells(j, 1).Formula
Worksheets("Sheet2").Cells(row, 2).Formula = Cells(j, 2).Formula
row = row + 1
End If
Next j
End Sub

 

[Tim Williams]

You could try adding a new column, using a UDF to populate the values.

Eg:

Function GetColor(c)
GetColor=c.interior.colorindex
End Function

Then filter on the new column

Tim.

 

[Norman Jones]

Hi Pawan,

Depending on your data configuration, you may be able to add a helper column
and use a function, provided by Chip Pearson, to return the background
colour. You could then filter on the helper column.

See Chip Pearson's 'Sorting By Color' page at:

http://www.cpearson.com/excel/SortByColor.htm

See particulatly Chip's ColorIndexOfCell function.

 

[Norman Jones]

Hi Tim,

My apologies - your post was not visible to me when I responded.

 

[Bob Phillips]

Ron de Bruin has an addin that filters by colour amongst other things, at
http://www.rondebruin.nl/easyfilter.htm. Scroll down to the foot of the page
to get the Beta version.

--

HTH

RP
(remove nothere from the email address if mailing direct)