Find word and bold

[ron]

Hello,

I would like help getting started on an Excel macro to find all the instances of a user specified word in a column also specified by the user and change the font formatting of the word to bold.

Regards
Ron

[Zack Barresse]

Hey Ron,


Maybe you can use something like this ...


Option Explicit

Sub MakeAllBold()
Dim searchRng As Range, strSearch As String, cel As Range
'** Change the range to suit
Set searchRng = Range("O5:O19")
strSearch = InputBox("What would you like to search for?", "Make Bold")
'If cancel was pressed or nothing was entered, quit running
If strSearch = "" Then Exit Sub
'** Loop through each cell in specified range
For Each cel In searchRng
'** Check the value against our new variable (user-entered value)
If cel.Value = strSearch Then
'** If matching, make it bold
cel.Font.Bold = True
'** If not, make it not bold, used mostly for running multiple times
Else: cel.Font.Bold = False
End If
Next cel
End Sub

Just put it into a standard Module and run from there. HTH

[ron]

firefytr

Thanks for the reply. I should have been more specific. Your code works great if one value is in each cell, but I?m working with cells that have one or more sentences, and I would like to bold just the one word the user enters in the input box.

Ron

[Zack Barresse]

Care to give some examples?

[ron]

Below is an example of the text entered in a cell and I want to bold the word shall.

The contractor shall perform the specific tasks described in paragraph 4.0 and summarized in the table below. (Reference Table 2-2 page 1)

[Zack Barresse]

Okay then, make use of the Characters mehtod ..


Sub MakeWordBold()
Dim strSearch As String, searchRng As Range, i As Long, cel As Range
Set searchRng = Range("A1:A10")
strSearch = InputBox("Please enter the text to make bold:", "Bold Text")
If strSearch = "" Then Exit Sub
For Each cel In searchRng
With cel
.Font.Bold = False
For i = 1 To Len(.Text) - Len(strSearch) Step 1
If Mid(.Text, i, Len(strSearch)) = strSearch Then
.Characters(i, Len(strSearch)).Font.Bold = True
End If
Next i
End With
Next cel
End Sub

[ron]

firefytr

Works fine. Thanks for the help!

Best regards

Ron

[Zack Barresse]

No problem. Glad to help. :)

Btw, did you know you can mark your own threads as Solved? Go to Thread Tools --> Mark Solved --> Perform Action. Take care!