Hello,
I have a column of data (data are numbers and text) in Excel (around 1000+
items) and they have different font, font size and color. How to use
Autofilter/Advance Filter or any other means, to filter out say all items
that are RED in color, or items with Times Roman as Font, or items with Font
size 10.
Pls help!

Thanks.

Well, here's a slightly convoluted approach.

Define a function in visual basic like the one below. (It just returns
true if any one of the cells in the selected range has red text in it.)

Function ItsRed(selectedRange As Range) As Boolean

Dim r As Long
Dim c As Long

On Error GoTo oops
If False Then
oops:
Exit Function
End If

ItsRed = False

For c = 1 To selectedRange.Columns.Count
For r = 1 To selectedRange.Rows.Count
If selectedRange.Cells(r, c).Font.Color = vbRed Then
ItsRed = True
End If
Next r
Next c
End Function


insert a column (say, B) next to your column of text (in A), and insert
the function like this:

=ItsRed(a2)

assuming that the text is in column A and the formula is in column B.
Notice in this case, the selected range passed to ItsRed is a single
cell, a2.

Then filter for true in column b.

I suspect that there are simpler ways to do it, but they're not leaping
out at me tonight. You should be able to test for other attributes
similarly, and filter accordingly.

Good luck,
Ed

... or speaking of simpler, just return the color instead of true /
false and filter for that.

Ed

wrote:
Well, here's a slightly convoluted approach.

Define a function in visual basic like the one below. (It just returns
true if any one of the cells in the selected range has red text in it.)

Function ItsRed(selectedRange As Range) As Boolean

Dim r As Long
Dim c As Long

On Error GoTo oops
If False Then
oops:
Exit Function
End If

ItsRed = False

For c = 1 To selectedRange.Columns.Count
For r = 1 To selectedRange.Rows.Count
If selectedRange.Cells(r, c).Font.Color = vbRed Then
ItsRed = True
End If
Next r
Next c
End Function


insert a column (say, B) next to your column of text (in A), and insert
the function like this:

=ItsRed(a2)

assuming that the text is in column A and the formula is in column B.
Notice in this case, the selected range passed to ItsRed is a single
cell, a2.

Then filter for true in column b.

I suspect that there are simpler ways to do it, but they're not leaping
out at me tonight. You should be able to test for other attributes
similarly, and filter accordingly.

Good luck,
Ed

Hi
You can use edit - serch instead of filter. In the search you can chose the
format you want to find, and then you can search for all matches.

Mia

"Jrufin" skrev:

Hello,
I have a column of data (data are numbers and text) in Excel (around 1000+
items) and they have different font, font size and color. How to use
Autofilter/Advance Filter or any other means, to filter out say all items
that are RED in color, or items with Times Roman as Font, or items with Font
size 10.
Pls help!

Thanks.

Thanks i'll try also..

"Mia" wrote:

Hi
You can use edit - serch instead of filter. In the search you can chose the
format you want to find, and then you can search for all matches.

Mia

"Jrufin" skrev:

Hello,
I have a column of data (data are numbers and text) in Excel (around 1000+
items) and they have different font, font size and color. How to use
Autofilter/Advance Filter or any other means, to filter out say all items
that are RED in color, or items with Times Roman as Font, or items with Font
size 10.
Pls help!

Thanks.

Thanks i'll try...

" wrote:

Well, here's a slightly convoluted approach.

Define a function in visual basic like the one below. (It just returns
true if any one of the cells in the selected range has red text in it.)

Function ItsRed(selectedRange As Range) As Boolean

Dim r As Long
Dim c As Long

On Error GoTo oops
If False Then
oops:
Exit Function
End If

ItsRed = False

For c = 1 To selectedRange.Columns.Count
For r = 1 To selectedRange.Rows.Count
If selectedRange.Cells(r, c).Font.Color = vbRed Then
ItsRed = True
End If
Next r
Next c
End Function


insert a column (say, B) next to your column of text (in A), and insert
the function like this:

=ItsRed(a2)

assuming that the text is in column A and the formula is in column B.
Notice in this case, the selected range passed to ItsRed is a single
cell, a2.

Then filter for true in column b.

I suspect that there are simpler ways to do it, but they're not leaping
out at me tonight. You should be able to test for other attributes
similarly, and filter accordingly.

Good luck,
Ed