Find and boldface a single word everywhere in a worksheet

[Artis Tiedemann]

I want to write a formula that will locate a word everywhere it appears in a
worksheet and boldface it. I know that I can use 'find' and then boldface
to accomplish this but I have a extremely large worksheet and would like to
speed up the process. My knowledge of Excel is not advanced enough to even
know where to begin.

Is there a way to do this?

Artis

 

[Ron Rosenfeld]

I want to write a formula that will locate a word everywhere it appears in a
worksheet and boldface it. I know that I can use 'find' and then boldface
to accomplish this but I have a extremely large worksheet and would like to
speed up the process. My knowledge of Excel is not advanced enough to even
know where to begin.

Is there a way to do this?

Artis

You will only be able to do this if the word you want to have boldfaced is
either the ONLY word in the cell, or, if it is part of a string, the string is
entered directly, and not as the result of a formula.

If the word is the result of a formula, and is also part of a multiword string,
it can still be bolded, but the original formula will have to be replaced by
the text string.

If these restrictions are satisfactory, please post back with more info as to
the characteristics of the cells containing this word.
--ron

 

[ShaneDevenshire]

Hi,

You can do this with VBA or manually, but not a spreadsheet function. And a
formula can not be formatted by character only the entire thing.

 

[Artis Tiedemann]

The word to boldface is in a text string that is entered directly and not a
result of a formula.

 

[Ron Rosenfeld]

The word to boldface is in a text string that is entered directly and not a
result of a formula.

OK, you won't be able to do it with a formula, as functions cannot alter the
environment.

You will need to do it with a VBA Macro.

Here's one example.

<alt-F11> opens the VB Editor.

Ensure your project is highlighted in the Project Explorer window, then
Insert/Module and paste the code below into the window that opens.

To use this, <alt-F8> opens the macro dialog box. Select the macro and RUN. It
will prompt you for the word to bold and color red.

It will bold all the strings that appear in the range that match what you
enter.

If you want to exclude those strings that are part of other strings:

e.g. bold TIME but not the TIME in TIMELY, a bit more programming will be
required.

==================================
Option Explicit
Sub BoldWord()
Dim sWordToBold As String, lWordLength As Long
Dim rRangeToSearch As Range
Dim c As Range
Dim i As Long

sWordToBold = InputBox("what word do you want to Bold?")
lWordLength = Len(sWordToBold)
Set rRangeToSearch = Range("A1:B10")

For Each c In rRangeToSearch
c.Font.Bold = False
c.Font.Color = vbBlack
i = 1
Do Until i = Len(c.Text)
i = InStr(i, c.Text, sWordToBold, vbTextCompare)
If i = 0 Then Exit Do
With c.Characters(i, lWordLength).Font
.Bold = True
.Color = vbRed
End With
i = i + 1
Loop
Next c

End Sub
======================================
--ron