ExcelBanter - Macro Help

Are you bolding the whole cell? (It's a lot easier if you say yes!)

This worked ok for me:

Option Explicit
Sub DoReplace()

Dim myLookFor As Variant
Dim myReplacements As Variant
Dim wks As Worksheet
Dim FoundCell As Range
Dim iCtr As Long
Dim FirstAddress As String

myLookFor = Array("RED VINES BLACK LICORICE LICORICE TWIST 64OZ 4136400103", _
"RED VINES RED LICORICE LICORICE TWIST 64OZ 4136400104", _
"RED VINES RED LICORICE LICORICE TWIST 2OZ 4136400222", _
"RED VINES BLACK LICORICE LICORICE TWIST 8OZ 4136400231", _
"RED VINES RED LICORICE LICORICE TWIST 8OZ 4136400232", _
"RED VINES ASSORTED LICORICE LICORICE BITE 8OZ 4136400240", _
"RED VINES GRAPE LICORICE TWIST 8OZ 4136400246", _
"RED VINES BLUE RASPBERRY LICORICE TWIST 8OZ 4136400247", _
"RED VINES WATERMELON LICORICE TWIST 8OZ 4136400248", _
"RED VINES STRAWBERRY LICORICE TWIST 16OZ 4136400251", _
"RED VINES CHERRY LICORICE TWIST 16OZ 4136400255", _
"RED VINES STRAWBERRY LICORICE TWIST 5.5OZ 4136400271", _
"RED VINES RED LICORICE LICORICE TWIST 6.5OZ 4136400273", _
"RED VINES CHERRY LICORICE TWIST 5.5OZ 4136400275")

myReplacements = Array("RED VINES JAR BLACK LICORICE TWIST 64OZ 4136400103", _
"RED VINES JAR RED TWIST 64OZ 4136400104", _
"RED VINES RED TWIST 2OZ 4136400222", _
"RED VINES BLACK LICORICE TWIST 8OZ 4136400231", _
"RED VINES RED TWIST 8OZ 4136400232", _
"RED VINES MIXED BITES 8OZ 4136400240", _
"RED VINES GRAPE TWIST 8OZ 4136400246", _
"RED VINES BLUE RASPBERRY TWIST 8OZ 4136400247", _
"RED VINES WATERMELON TWIST 8OZ 4136400248", _
"RED VINES JUMBO STRAWBERRY TWIST 16OZ 4136400255", _
"RED VINES JUMBO CHERRY TWIST 16OZ 4136400255", _
"RED VINES TRAY STRAWBERRY TWIST 5.5OZ 4136400271", _
"RED VINES RED TWIST 6.5OZ 4136400273", _
"RED VINES TRAY CHERRY TWIST 5.5OZ 4136400275")

If UBound(myLookFor) < UBound(myReplacements) Then
MsgBox "Design error! Not same number of elements!"
Exit Sub
End If

Set wks = ActiveSheet

With wks

For iCtr = LBound(myLookFor) To UBound(myLookFor)
Set FoundCell = .Cells.Find(What:=myLookFor(iCtr), _
after:=.Cells(.Cells.Count), LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlNext, searchdirection:=xlNext, _
MatchCase:=False)
If FoundCell Is Nothing Then
'do nothing
Else
FirstAddress = FoundCell.Address

Do
FoundCell.Font.Bold = True
Set FoundCell = .Cells.FindNext(FoundCell)
Loop While Not FoundCell Is Nothing _
And FoundCell.Address < FirstAddress

.Cells.Replace What:=myLookFor(iCtr), _
Replacement:=myReplacements(iCtr), _
MatchCase:=False, SearchOrder:=xlNext
End If
Next iCtr

End With

End Sub

It actually finds all the strings and bolds those cells. Then does the
replacement.

ALC wrote:

Hello,

The macro bellow was written to find and replace text within a work
sheet. I also need it to bold those replacements.

Can anyone help?

Thank you,
ALC :)

Macro-

Sub Replace()
'
' Replace Macro
' Macro recorded 7/27/2005 by jthomas
'
' Keyboard Shortcut: Ctrl+z
'ActiveSheet.Select

Cells.Replace What:="RED VINES BLACK LICORICE LICORICE TWIST 64OZ
4136400103" _
, Replacement:="RED VINES JAR BLACK LICORICE TWIST 64OZ
4136400103", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False
Cells.Replace What:="RED VINES RED LICORICE LICORICE TWIST 64OZ
4136400104", _
Replacement:="RED VINES JAR RED TWIST 64OZ 4136400104",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Cells.Replace What:="RED VINES RED LICORICE LICORICE TWIST 2OZ
4136400222", _
Replacement:="RED VINES RED TWIST 2OZ 4136400222",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Cells.Replace What:="RED VINES BLACK LICORICE LICORICE TWIST 8OZ
4136400231", _
Replacement:="RED VINES BLACK LICORICE TWIST 8OZ 4136400231",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Cells.Replace What:="RED VINES RED LICORICE LICORICE TWIST 8OZ
4136400232", _
Replacement:="RED VINES RED TWIST 8OZ 4136400232",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Cells.Replace What:="RED VINES ASSORTED LICORICE LICORICE BITE 8OZ
4136400240", _
Replacement:="RED VINES MIXED BITES 8OZ 4136400240",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Cells.Replace What:="RED VINES GRAPE LICORICE TWIST 8OZ 4136400246", _
Replacement:="RED VINES GRAPE TWIST 8OZ 4136400246",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Cells.Replace What:="RED VINES BLUE RASPBERRY LICORICE TWIST 8OZ
4136400247", _
Replacement:="RED VINES BLUE RASPBERRY TWIST 8OZ 4136400247",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Cells.Replace What:="RED VINES WATERMELON LICORICE TWIST 8OZ
4136400248", _
Replacement:="RED VINES WATERMELON TWIST 8OZ 4136400248",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Cells.Replace What:="RED VINES STRAWBERRY LICORICE TWIST 16OZ
4136400251", _
Replacement:="RED VINES JUMBO STRAWBERRY TWIST 16OZ
4136400251", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Cells.Replace What:="RED VINES CHERRY LICORICE TWIST 16OZ 4136400255",
_
Replacement:="RED VINES JUMBO CHERRY TWIST 16OZ 4136400255",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Cells.Replace What:="RED VINES STRAWBERRY LICORICE TWIST 5.5OZ
4136400271", _
Replacement:="RED VINES TRAY STRAWBERRY TWIST 5.5OZ
4136400271", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Cells.Replace What:="RED VINES RED LICORICE LICORICE TWIST 6.5OZ
4136400273", _
Replacement:="RED VINES RED TWIST 6.5OZ 4136400273",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Cells.Replace What:="RED VINES CHERRY LICORICE TWIST 5.5OZ 4136400275",
_
Replacement:="RED VINES TRAY CHERRY TWIST 5.5OZ 4136400275",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

End Sub

--
ALC
------------------------------------------------------------------------
ALC's Profile: http://www.excelforum.com/member.php...o&userid=25360
View this thread: http://www.excelforum.com/showthread...hreadid=394032

--

Dave Peterson