Changing text color of certain "word" in cell

[azu_daioh]

Is there a way to change the text color of a certain word in a cell?

For example, this is what cell A7 contains:
"placed on 10 years probation suspended pending passing a psychiatric
and medical evaluations and during probation is prohibited from
supervising physician assistants and effective June 15 2008 at 5:00 pm
the license is suspended for 60 days."

If the cell contains the word "suspended", I would like it to change
color to red...but only the word "suspended" and not the entire cell.

I tried searching for conditional formatting or VB but came up with
nothing. I'm not really sure the keywords to use, so maybe that's why
I'm not coming up with anything.

Is this possible? I appreciate any help I could get.


Thank you,
Sharon

 

[Ron Rosenfeld]

Is there a way to change the text color of a certain word in a cell?

For example, this is what cell A7 contains:
"placed on 10 years probation suspended pending passing a psychiatric
and medical evaluations and during probation is prohibited from
supervising physician assistants and effective June 15 2008 at 5:00 pm
the license is suspended for 60 days."

If the cell contains the word "suspended", I would like it to change
color to red...but only the word "suspended" and not the entire cell.

I tried searching for conditional formatting or VB but came up with
nothing. I'm not really sure the keywords to use, so maybe that's why
I'm not coming up with anything.

Is this possible? I appreciate any help I could get.


Thank you,
Sharon

You can either do it manually, by highlighting the word "suspended" and then
selecting Red for the text color, or you can do it with a VBA Macro.

To enter the macro, <alt-F11> opens the VB Editor. Ensure your project is
highlighted in the project explorer window, then select Insert/Module and paste
the code below into the window that opens.


To use this, select the cell(s) that need to be processed.

<alt-F8> opens the macro dialog box.

Then select Red and <Run>

-------------------------
Option Explicit
Sub Red()
Dim c As Range
Dim r As Range
Dim sWord As String
Dim Start As Long

sWord = "suspended"

Set r = Selection

For Each c In r
With c
.Font.Color = vbBlack
Start = 1 - Len(sWord)
Do
Start = InStr(Start + Len(sWord), .Value, sWord, vbTextCompare)
If Start > 0 Then
.Characters(Start, Len(sWord)).Font.Color = vbRed
End If
Loop Until Start = 0
End With
Next c
End Sub
=================================
--ron

 

[azu_daioh]

Thanks Ron. That did the trick. I added it in my current macro and
it worked perfectly. Thanks again.

 

[Ron Rosenfeld]

Thanks Ron. That did the trick. I added it in my current macro and
it worked perfectly. Thanks again.

You're most welcome. Thanks for the feedback.
--ron