Hi,

I would like to run a macro that looks at each word in a cell, and if
that word is in red, delete the word

Can anyone help me do this?

Without actually doing this I can tell you how I would go about finding
the solution:
Turn on your macro recorder.
Type some words in a cell, making some letters red in the middle of the text.
Turn off the recorder.

Look at the recorded code to see what property makes the font red for
certain letters

Then use that property to loop through the text in a cell and delete
any character with that property.

Hope this helps get you started

"kaiser" wrote:

Hi,

I would like to run a macro that looks at each word in a cell, and if
that word is in red, delete the word

Can anyone help me do this?

This might do it but you may be left with extra spaces.

Sub DelRed()
Dim i As Integer
Dim RStr As String
For i = 1 To Len(ActiveCell.Value)
If ActiveCell.Characters(i, 1).Font.ColorIndex < 3 Then
RStr = RStr & Mid(ActiveCell.Value, i, 1)
End If
Next i
ActiveCell.Value = RStr
End Sub

Hope this helps

Regards
Rowan

cush wrote:
Without actually doing this I can tell you how I would go about finding
the solution:
Turn on your macro recorder.
Type some words in a cell, making some letters red in the middle of the text.
Turn off the recorder.

Look at the recorded code to see what property makes the font red for
certain letters

Then use that property to loop through the text in a cell and delete
any character with that property.

Hope this helps get you started

"kaiser" wrote:


Hi,

I would like to run a macro that looks at each word in a cell, and if
that word is in red, delete the word

Can anyone help me do this?

Interesting. For this demo I have some text in H7 , "This is a test for
color", where the word test is colored red.
On running th eprocedure called test, the clee's value becomes 'this is a
for color"

Option Explicit
Sub test()

Dim target As Range
Set target = Range("H7")
target.Value = RemoveColor(target, vbRed)

End Sub

Function RemoveColor(source As Range, iColor As Long) As String
Dim text As Variant, bit As String
Dim index As Long
Dim pos As Long
text = Split(source, " ")
For index = LBound(text, 1) To UBound(text, 1)
pos = InStr(pos + 1, source, text(index))
If source.Characters(pos, Len(text(index))).Font.Color = iColor Then
text(index) = ""
End If
Next
RemoveColor = Join(text, " ")
End Function


"kaiser" wrote:

Hi,

I would like to run a macro that looks at each word in a cell, and if
that word is in red, delete the word

Can anyone help me do this?