Search & Replace Formatting for part of cell
 

Good Morning,

Win XP
Excel '03

I need to do a search and replace for formatting for a single word in a cell
of multiple words. Ex. I want to replace Textile <no formatting with
Textile <strikethrough and textile may not always be the only word in the
cell. When I do a standart S&R it formats the whole cell, I only want the
word. I figured this would have to be accomplished with VB code. Please
advise.

Search & Replace Formatting for part of cell
 

How about just using a macro that would strikethrough all the words you give it
for a given selection:

Option Explicit
Option Compare Text
Sub testme()

Application.ScreenUpdating = False

Dim myWords As Variant
Dim myRng As Range
Dim foundCell As Range
Dim iCtr As Long 'word counter
Dim cCtr As Long 'character counter
Dim FirstAddress As String
Dim AllFoundCells As Range
Dim myCell As Range

'add other words here
myWords = Array("textiles")

Set myRng = Selection

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

If myRng Is Nothing Then
MsgBox "Please choose a range that contains text constants!"
Exit Sub
End If

For iCtr = LBound(myWords) To UBound(myWords)
FirstAddress = ""
Set foundCell = Nothing
With myRng
Set foundCell = .Find(what:=myWords(iCtr), _
LookIn:=xlValues, lookat:=xlPart, _
after:=.Cells(.Cells.Count))

If foundCell Is Nothing Then
MsgBox myWords(iCtr) & " wasn't found!"
Else
Set AllFoundCells = foundCell
FirstAddress = foundCell.Address
Do
If AllFoundCells Is Nothing Then
Set AllFoundCells = foundCell
Else
Set AllFoundCells = Union(foundCell, AllFoundCells)
End If
Set foundCell = .FindNext(foundCell)

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

End With

If AllFoundCells Is Nothing Then
'do nothing
Else
For Each myCell In AllFoundCells.Cells
For cCtr = 1 To Len(myCell.Value)
If Mid(myCell.Value, cCtr, Len(myWords(iCtr))) _
= myWords(iCtr) Then
myCell.Characters(Start:=cCtr, _
Length:=Len(myWords(iCtr))) _
.Font.Strikethrough = True
End If
Next cCtr
Next myCell
End If
Next iCtr
Application.ScreenUpdating = True

End Sub


Tanya B wrote:

Good Morning,

Win XP
Excel '03

I need to do a search and replace for formatting for a single word in a cell
of multiple words. Ex. I want to replace Textile <no formatting with
Textile <strikethrough and textile may not always be the only word in the
cell. When I do a standart S&R it formats the whole cell, I only want the
word. I figured this would have to be accomplished with VB code. Please
advise.

--

Dave Peterson

Search & Replace Formatting for part of cell
 

ps...

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Tanya B wrote:

Good Morning,

Win XP
Excel '03

I need to do a search and replace for formatting for a single word in a cell
of multiple words. Ex. I want to replace Textile <no formatting with
Textile <strikethrough and textile may not always be the only word in the
cell. When I do a standart S&R it formats the whole cell, I only want the
word. I figured this would have to be accomplished with VB code. Please
advise.

--

Dave Peterson

Search & Replace Formatting for part of cell
 

This work perfect. Thank you very much.

"Dave Peterson" wrote:

How about just using a macro that would strikethrough all the words you give it
for a given selection:

Option Explicit
Option Compare Text
Sub testme()

Application.ScreenUpdating = False

Dim myWords As Variant
Dim myRng As Range
Dim foundCell As Range
Dim iCtr As Long 'word counter
Dim cCtr As Long 'character counter
Dim FirstAddress As String
Dim AllFoundCells As Range
Dim myCell As Range

'add other words here
myWords = Array("textiles")

Set myRng = Selection

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

If myRng Is Nothing Then
MsgBox "Please choose a range that contains text constants!"
Exit Sub
End If

For iCtr = LBound(myWords) To UBound(myWords)
FirstAddress = ""
Set foundCell = Nothing
With myRng
Set foundCell = .Find(what:=myWords(iCtr), _
LookIn:=xlValues, lookat:=xlPart, _
after:=.Cells(.Cells.Count))

If foundCell Is Nothing Then
MsgBox myWords(iCtr) & " wasn't found!"
Else
Set AllFoundCells = foundCell
FirstAddress = foundCell.Address
Do
If AllFoundCells Is Nothing Then
Set AllFoundCells = foundCell
Else
Set AllFoundCells = Union(foundCell, AllFoundCells)
End If
Set foundCell = .FindNext(foundCell)

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

End With

If AllFoundCells Is Nothing Then
'do nothing
Else
For Each myCell In AllFoundCells.Cells
For cCtr = 1 To Len(myCell.Value)
If Mid(myCell.Value, cCtr, Len(myWords(iCtr))) _
= myWords(iCtr) Then
myCell.Characters(Start:=cCtr, _
Length:=Len(myWords(iCtr))) _
.Font.Strikethrough = True
End If
Next cCtr
Next myCell
End If
Next iCtr
Application.ScreenUpdating = True

End Sub


Tanya B wrote:

Good Morning,

Win XP
Excel '03

I need to do a search and replace for formatting for a single word in a cell
of multiple words. Ex. I want to replace Textile <no formatting with
Textile <strikethrough and textile may not always be the only word in the
cell. When I do a standart S&R it formats the whole cell, I only want the
word. I figured this would have to be accomplished with VB code. Please
advise.

--

Dave Peterson