asked on

VBA code that formats a word (that word only) if it is present in each cell of a specified range

How do I write a code that says in effect: Look for the string "27D" inside each cell in a specidied range, and make that string Red Bold? This sample code is just to give you a better idea of what I'm looking for

Thanks,
John

Sub FormatText()
Dim str As String
'str = find "27D" within the cell and make it Red Bold
For Each cell In Range("B2:B200")
str.Font.ColorIndex = 3 'I know this is all wrong
str.Bold = True 'I know this is all wrong

Next
End Sub

Open in new window

ASKER CERTIFIED SOLUTION

SiddharthRout

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

John Carney

ASKER

Hi Sid, thanks. This works great for coloring the entire cell, but I need something that only highlights "27D". I was going to post a comment but I couldn't get myself to stop trying to figure it out, and I managed to come with something surprisingly compact that works great.

I never would have gotten this except for your post, so thanks.

- John

Sub FormatText()
Dim cel As Range, str As String
str = [G1].Value
For Each cel In Range("D8:D194")
If InStr(cel.Value, str) <> 0 Then
cel.Characters(Start:=InStr(cel.Value, str), Length:=3).Font.Bold = True
cel.Characters(Start:=InStr(cel.Value, str), Length:=3).Font.ColorIndex = [G1].Font.ColorIndex
cel.Offset(0, -3).Interior.ColorIndex = 45
End If
Next
End Sub

Open in new window

John Carney

ASKER

I tried closing this 2 hours ago but I couldn't. Thanks again!

- John