Search and Replace

  • Thread starter Thread starter Judy
  • Start date Start date
J

Judy

Is there a way in Excel to do a replace that will apply
bold to text, the way you can in Word. Is there a code I
would use in the Replace field to signify bold. Thanks
 
Not built into excel. You could use a macro, though:

Option Explicit
Sub testme01()

Dim myWords As Variant
Dim myCell As Range
Dim myRng As Range
Dim FirstAddress As String
Dim iCtr As Long
Dim letCtr As Long

On Error Resume Next
Set myRng = Intersect(Selection, _
Selection.Cells _
.SpecialCells(xlCellTypeConstants, xlTextValues))
On Error GoTo 0

myWords = Array("test", "bold", "hilight")

If myRng Is Nothing Then
MsgBox "No Text Cells found in Selection"
Exit Sub
End If

For iCtr = LBound(myWords) To UBound(myWords)
With myRng
Set myCell = .Find(What:=myWords(iCtr), After:=.Cells(1), _
LookIn:=xlValues, LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

If Not myCell Is Nothing Then
FirstAddress = myCell.Address
Do
For letCtr = 1 To Len(myCell.Value)
If StrComp(Mid(myCell.Value, letCtr, _
Len(myWords(iCtr))), _
myWords(iCtr), vbTextCompare) = 0 Then
myCell.Characters(Start:=letCtr, _
Length:=Len(myWords(iCtr))) _
.Font.FontStyle = "Bold"
End If
Next letCtr

Set myCell = .FindNext(myCell)

Loop While Not myCell Is Nothing _
And myCell.Address <> FirstAddress
End If

End With
Next iCtr

End Sub

You can just put the words you need to highlight in this line:

myWords = Array("test", "bold", "hilight")

(one word is ok, too.)

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
Thanks, I will give it a try.
-----Original Message-----
Not built into excel. You could use a macro, though:

Option Explicit
Sub testme01()

Dim myWords As Variant
Dim myCell As Range
Dim myRng As Range
Dim FirstAddress As String
Dim iCtr As Long
Dim letCtr As Long

On Error Resume Next
Set myRng = Intersect(Selection, _
Selection.Cells _
.SpecialCells
Click to expand...
(xlCellTypeConstants, xlTextValues))
 
Thanks so much this worked perfectly.
-----Original Message-----
Not built into excel. You could use a macro, though:

Option Explicit
Sub testme01()

Dim myWords As Variant
Dim myCell As Range
Dim myRng As Range
Dim FirstAddress As String
Dim iCtr As Long
Dim letCtr As Long

On Error Resume Next
Set myRng = Intersect(Selection, _
Selection.Cells _
.SpecialCells
Click to expand...
(xlCellTypeConstants, xlTextValues))
 
Back
Top