Bolding a certain word throughout worksheet?

[suestew]

How do I do it?

 

[Brotha Lee]

Suestew,

Use the conditional formatting which can be found under Format Menu.


Regards,

Brotha lee

 

[Peter T]

Sub Test()

BoldWord ActiveSheet, "my-word"

End Sub


Sub BoldWord(ws As Worksheet, sWord As String)
Dim pos As Long
Dim firstAddress As String
Dim vBold
Dim rng As Range
Dim cel As Range

On Error Resume Next
Set rng = ActiveSheet.Cells.SpecialCells(xlCellTypeConstants, 2)
On Error GoTo 0
If rng Is Nothing Then Exit Sub

With rng

Set cel = .Find(what:=sWord, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
If Not cel Is Nothing Then
firstAddress = cel.Address
Do
pos = InStr(1, cel.Value, sWord)
While pos
vBold = cel.Characters(pos, Len(sWord)).Font.Bold
If vBold = vbNull Then vBold = False
If Not vBold Then
cel.Characters(pos, Len(sWord)).Font.Bold = True
End If

pos = InStr(pos + 1, cel.Value, sWord)
Wend

Set cel = .FindNext(cel)
Loop While Not cel Is Nothing And cel.Address <> firstAddress

End If
End With

End Sub

Regards,
Peter T

 

[Gord Dibben]

Is the word part of a string or by itself?

Do you want to do it Via VBA or just use Conditinal Formatting?

Since you posted in the programming group, here is a macro for VBA method no
matter where the word is located on the sheet.

Sub Bold_Word()
Dim rng As Range
Dim Cell As Range
Dim myword As String
Dim start_str As Integer
Dim Mylen As Integer
Dim N As Single
myword = InputBox("Enter the word ")
If myword = "" Then Exit Sub
Mylen = Len(myword)
Set rng = ActiveSheet.UsedRange
For Each Cell In rng
Cell.Font.Bold = False
start_str = InStr(Cell.Value, myword)
If start_str Then
Cell.Characters(start_str, Mylen).Font.Bold = True
End If
Next Cell
End Sub


Gord Dibben MS Excel MVP

 

[Peter T]

Probably want case insensitive

Change

pos = InStr(1, cel.Value, sWord)

to
pos = InStr(1, cel.Value, sWord, vbTextCompare)
and change

pos = InStr(pos + 1, cel.Value, sWord)

to
pos = InStr(pos + 1, cel.Value, sWord, vbTextCompare)

As written the routine only looks for the word in string constant cells, not
formulas.

Peter T

 

[JLGWhiz]

Hey Gord, I like that one. Think I'll save it for future use.

 

[Gord Dibben]

Thanks

Add this line to top of module.

Option Compare Text


Gord