change to existing vba needed

[cjcass]

Hi,
Hope you can help... I have the code below which changes the font format of a specific chosen word wherever it appears across the selected range of cells. The word which requires a format change is determined by typing it into a prompt box, however I want the vba to refer to the text in cell D3 to get the word and to bin the prompt box - hope this makes sense, any help greatly appreciated!

Dim cl As Range
Dim SearchText As String
Dim StartPos As Integer
Dim EndPos As Integer
Dim TestPos As Integer
Dim TotalLen As Integer


On Error Resume Next
Application.DisplayAlerts = False
SearchText = Application.InputBox _
(Prompt:="Enter string.", Title:="Which string to format?", Type:=2)
On Error GoTo 0
Application.DisplayAlerts = True
If SearchText = "" Then
Exit Sub
Else
For Each cl In Selection
TotalLen = Len(SearchText)
StartPos = InStr(UCase(cl), UCase(SearchText))
TestPos = 0
Do While StartPos > TestPos
With cl.Characters(StartPos, TotalLen).Font
.FontStyle = "Bold"
.ColorIndex = 5
End With
EndPos = StartPos + TotalLen
TestPos = TestPos + EndPos
StartPos = InStr(TestPos, cl, SearchText, vbTextCompare)
Loop
Next cl
End If

 

[Rick Rothstein]

Hi,
Hope you can help... I have the code below which changes the font format of a specific chosen word wherever it appears across the selected range of cells. The word which requires a format change is determined by typing it into a prompt box, however I want the vba to refer to the text in cell D3 to get the word and to bin the prompt box - hope this makes sense, any help greatly appreciated!

Dim cl As Range
Dim SearchText As String
Dim StartPos As Integer
Dim EndPos As Integer
Dim TestPos As Integer
Dim TotalLen As Integer


On Error Resume Next
Application.DisplayAlerts = False
SearchText = Application.InputBox _
(Prompt:="Enter string.", Title:="Which string to format?", Type:=2)
On Error GoTo 0
Application.DisplayAlerts = True
If SearchText = "" Then
Exit Sub
Else
For Each cl In Selection
TotalLen = Len(SearchText)
StartPos = InStr(UCase(cl), UCase(SearchText))
TestPos = 0
Do While StartPos > TestPos
With cl.Characters(StartPos, TotalLen).Font
.FontStyle = "Bold"
.ColorIndex = 5
End With
EndPos = StartPos + TotalLen
TestPos = TestPos + EndPos
StartPos = InStr(TestPos, cl, SearchText, vbTextCompare)
Loop
Next cl
End If

Replace what I highlighted in red with this...

SearchText = Range("D3").Value

 

[Norie]

Change this,

SearchText = Application.InputBox _
(Prompt:="Enter string.", Title:="Which string to format?", Type:=2)

to this.

SearchText = Range("D3").Value

 

[cjcass]

Great, many thanks!

 

[cjcass]

Hi again if you're still around - I've just noticed that if the chosen word for formatting is the FIRST word in the text string in the cell it formats ALL the words in the cell and not just the specific word in D3 - any ideas to get around this??