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!