Bolding a certain word throughout worksheet?
 

How do I do it?

Bolding a certain word throughout worksheet?
 

Suestew,

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


Regards,

Brotha lee

"suestew" wrote:

How do I do it?

Bolding a certain word throughout worksheet?
 

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

"suestew" wrote in message
...
How do I do it?

Bolding a certain word throughout worksheet?
 

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

On Mon, 15 Dec 2008 12:11:02 -0800, suestew
wrote:

How do I do it?

Bolding a certain word throughout worksheet?
 

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


"Peter T" <peter_t@discussions wrote in message
...
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

"suestew" wrote in message
...
How do I do it?

Bolding a certain word throughout worksheet?
 

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

"Gord Dibben" wrote:

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

On Mon, 15 Dec 2008 12:11:02 -0800, suestew
wrote:

How do I do it?

Bolding a certain word throughout worksheet?
 

Thanks

Add this line to top of module.

Option Compare Text


Gord

On Mon, 15 Dec 2008 15:40:09 -0800, JLGWhiz
wrote:

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

"Gord Dibben" wrote:

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

On Mon, 15 Dec 2008 12:11:02 -0800, suestew
wrote:

How do I do it?