Bold a certain word throughout worksheet
 

How do I bold a particular word throughout my entire worksheet?

Bold a certain word throughout worksheet
 

Try this one for the word ron

Sub MakeBold()
Dim FirstAddress As String
Dim MyArr As Variant
Dim Rng As Range
Dim I As Long

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

'Search for a Value Or Values in a range
'You can also use more values like this Array("ron", "dave")
MyArr = Array("ron")

'Search range
With Sheets("Sheet1").UsedRange


For I = LBound(MyArr) To UBound(MyArr)

'If you want to find a part of the rng.value then use xlPart
'if you use LookIn:=xlValues it will also work with a
'formula cell that evaluates to "ron"

Set Rng = .Find(What:=MyArr(I), _
After:=.Cells(.Cells.Count), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

If Not Rng Is Nothing Then
FirstAddress = Rng.Address
Do
Rng.Font.Bold = True
Set Rng = .FindNext(Rng)
Loop While Not Rng Is Nothing And Rng.Address < FirstAddress
End If
Next I
End With

With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm




"suestew" wrote in message ...
How do I bold a particular word throughout my entire worksheet?

Bold a certain word throughout worksheet
 

A much simpler way is to do this:

Sheets("Sheet1").Select 'Or whatever worksheet it is
With Application.ReplaceFormat.Font
.FontStyle = "Bold"
End With
Cells.Replace What:="Ron", Replacement:="Ron", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=True


"Ron de Bruin" wrote:

Try this one for the word ron

Sub MakeBold()
Dim FirstAddress As String
Dim MyArr As Variant
Dim Rng As Range
Dim I As Long

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

'Search for a Value Or Values in a range
'You can also use more values like this Array("ron", "dave")
MyArr = Array("ron")

'Search range
With Sheets("Sheet1").UsedRange


For I = LBound(MyArr) To UBound(MyArr)

'If you want to find a part of the rng.value then use xlPart
'if you use LookIn:=xlValues it will also work with a
'formula cell that evaluates to "ron"

Set Rng = .Find(What:=MyArr(I), _
After:=.Cells(.Cells.Count), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

If Not Rng Is Nothing Then
FirstAddress = Rng.Address
Do
Rng.Font.Bold = True
Set Rng = .FindNext(Rng)
Loop While Not Rng Is Nothing And Rng.Address < FirstAddress
End If
Next I
End With

With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm




"suestew" wrote in message ...
How do I bold a particular word throughout my entire worksheet?

Bold a certain word throughout worksheet
 

Hi LOFE

I agree with you in this case

With the macro I posted you can do a lot more but that is not needed in this case.

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm




"LOFE" wrote in message ...
A much simpler way is to do this:

Sheets("Sheet1").Select 'Or whatever worksheet it is
With Application.ReplaceFormat.Font
.FontStyle = "Bold"
End With
Cells.Replace What:="Ron", Replacement:="Ron", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=True


"Ron de Bruin" wrote:

Try this one for the word ron

Sub MakeBold()
Dim FirstAddress As String
Dim MyArr As Variant
Dim Rng As Range
Dim I As Long

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

'Search for a Value Or Values in a range
'You can also use more values like this Array("ron", "dave")
MyArr = Array("ron")

'Search range
With Sheets("Sheet1").UsedRange


For I = LBound(MyArr) To UBound(MyArr)

'If you want to find a part of the rng.value then use xlPart
'if you use LookIn:=xlValues it will also work with a
'formula cell that evaluates to "ron"

Set Rng = .Find(What:=MyArr(I), _
After:=.Cells(.Cells.Count), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

If Not Rng Is Nothing Then
FirstAddress = Rng.Address
Do
Rng.Font.Bold = True
Set Rng = .FindNext(Rng)
Loop While Not Rng Is Nothing And Rng.Address < FirstAddress
End If
Next I
End With

With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm




"suestew" wrote in message ...
How do I bold a particular word throughout my entire worksheet?