Extracting just words with color letter

[mariela]

Hi, two days I'm trying to find solution to this problem:

I have table in Excel (2003, WinXP) with text
every cell contains a few words. Just one of the words have red letter.
I need to extract words containg the red letter and sort them by abc.
Sorting is not the problem but extracting.

In Microsoft forums one smart guy help me to extract the red letter with this code:

=ColorWord(A1,3)

to extract the red letters from cell A1.

HTH,
Bernie
MS Excel MVP


Function ColorWord(myCell As Range, iColor As Integer)
Dim i As Integer
ColorWord = ""
With myCell
For i = 1 To Len(myCell.Text)
With .Characters(i, 1).Font
If .ColorIndex = iColor Then
ColorWord = ColorWord & Mid(myCell.Text, i, 1)
End If
End With
Next i
End With
End Function


I tried to changed it and nothing worked.
What I need to be chaged here is - range from A1:D5000
and extracting the whole word.

Thanks in advance,

Mariela

 

[R_Stephens]

Hi Mariela,

I'm afraid I'm not sure why you are getting that message! Is it a particular cell that is being looked at when the error message is appearing? From you saying its working it implies that it is bringing across some data onto Sheet 2 in the right format, so it must be a specific piece of data thats causing the error.

There might be a strange text string or something.

Richard