Move text that is a specific color to a different cell

A

Alison84

Hi,
I have a worksheet that has groups of text in the same cell. I have used
colors to group the text together. I now want to seperate the different
groups (colors) of text to a different cell depending on the color.

Is this possible? Any help would be great.

Thanks
 
G

Gary''s Student

In cell A1 enter:
Now is the time for all good men to come to the aid of their party.

Then color good men red.

Then enter and run this macro:

Sub red_picker()
Dim r1 As Range, r2 As Range
Dim s1 As String, s2 As String

Set r1 = Range("A1")
Set r2 = Range("A2")
s1 = r1.Value
l = Len(s1)

For i = 1 To l
If r1.Characters(i, 1).Font.ColorIndex = 3 Then
s2 = s2 & Mid(s1, i, 1)
End If
Next

r2.Value = s2
End Sub


This is just a sample of the technique, adapt it as you choose.
 
A

Alison84

Wow it works!!!!!

Thanks you so much - this will make my work much much easier now.

Alison84 :)
 
G

Gary''s Student

You are very welcome.

If you know the tricks, you can manipulate text as easily in Excel as in Word.
 
Top