Replace text format

[Matt]

I've got a customer that has a bunch of paragraphs within
cells. She is using the Find/Replace option, to search
for a particular word. She finds 5 instances of the word
and wants to replace the formatting of this ONE word to
standout against the rest of the cell's contents. When
she uses the Replace and sets the format to Bold and Red,
the entire cell's contents change to Bold red instead of
changing just the single word. Is there any way to do it
so that just the word and not the entire cell's contents
change?

 

[Guest]

Unfortunately, in this case it's not really an option.
It is a very large spreadsheet and contains about 20
worksheets and she's trying to search for this word on
all the sheets.

-----Original Message-----




Dear Matt,

Yes, tell your customer to write paragraphs in Word. Of

course, if that's not an option, your customer can
manually highlight the word to be changed and then click
on the font color and the bold button on the formatting
toolbar. I know this solution isn't high tech, but it
works and frankly it's faster than trying to figure out
how to make Excel do somethng that it wasn't originally
designed to do.

 

[Matt]

Unfortunately, in this case it's not really an option.
It is a very large spreadsheet and contains about 20
worksheets and she's trying to search for this word on
all the sheets.

-----Original Message-----




Dear Matt,

Yes, tell your customer to write paragraphs in Word. Of

course, if that's not an option, your customer can
manually highlight the word to be changed and then click
on the font color and the bold button on the formatting
toolbar. I know this solution isn't high tech, but it
works and frankly it's faster than trying to figure out
how to make Excel do somethng that it wasn't originally
designed to do.

 

[kkknie]

You could have them copy the spreadsheet to word, do the replace ther
and then copy back.

Other than that, I think your only option is to do it all manually (o
find someone to write you a fairly complicated VBA routine to do it).

 

[LarryP]

I just tried this out using the Macro Recorder and came up
with the following (spread over multiple lines due to the
scroll settings of this site):

ActiveCell.Characters(Start:=13,
Length:=11).Font.ColorIndex = 41 'this happens to be
blue, but of course you can set it to any color index
value you want; you could also set other format
characteristics such as bold or italic if that's what you
prefer.

Your challenge, of course, is to build a code loop that
(1) sets the ActiveCell in turn to each cell where your
target text is found, and (2) determines the Start and
Length values in each case.

 

[Guest]

Unfortunately, in this case it's not really an option.

It is a very large spreadsheet and contains about 20
worksheets and she's trying to search for this word on
all the sheets.

Dear Matt,

That's fine, she can still change the text manually while using the find feature of Excel. Just find the first occurrence, make your changes, click on find next, etc.

Again, this isn't high tech, and I'd be willing to write the VB to do it with the best of them. But sometimes, less is more, and in this case, working with one workbook, I'd think that doing the find thing and manually formatting up in the formula bar would be faster than writing and testing a macro.

Now if this is something that your customer has to do to 10 spreadsheets, every month, and they all contain 20 wroksheets, that's different. But this sounds like a one-and-done thing.

Bob Sullivan

 

[Guest]

Yep that's the case. It is a huge file and the number of
times that this word comes up is 445, spreading over 41
worksheets.

-----Original Message-----

Dear Matt,

That's fine, she can still change the text manually

while using the find feature of Excel. Just find the
first occurrence, make your changes, click on find next,
etc.

Again, this isn't high tech, and I'd be willing to write

the VB to do it with the best of them. But sometimes,
less is more, and in this case, working with one
workbook, I'd think that doing the find thing and
manually formatting up in the formula bar would be faster
than writing and testing a macro.

Now if this is something that your customer has to do to

10 spreadsheets, every month, and they all contain 20
wroksheets, that's different. But this sounds like a one-
and-done thing.

 

[Matt]

Yep that's the case. It is a huge file and the number of
times that this word comes up is 445, spreading over 41
worksheets.

-----Original Message-----

Dear Matt,

That's fine, she can still change the text manually

while using the find feature of Excel. Just find the
first occurrence, make your changes, click on find next,
etc.

Again, this isn't high tech, and I'd be willing to write

the VB to do it with the best of them. But sometimes,
less is more, and in this case, working with one
workbook, I'd think that doing the find thing and
manually formatting up in the formula bar would be faster
than writing and testing a macro.

Now if this is something that your customer has to do to

10 spreadsheets, every month, and they all contain 20
wroksheets, that's different. But this sounds like a one-
and-done thing.

 

[bj]

one way to do it is to use a macro similar to:

Sub emp()
For r = 1 To 10
samp = Cells(r, 1)
samlen = Len(Cells(r, 1))
For n = 1 To samlen - 3
If Mid(Cells(r, 1), n, 3) = "her" Then
Cells(r, 1).Select


With ActiveCell.Characters(Start:=n, Length:=3).Font

.ColorIndex = 3
End With
End If
Next n
Next r
End Sub

The cells to check would have to be set appropriately and
the word to check against and its length would have to be
made proper.

 

[Dave Peterson]

Here's a link to a routine that might help (it only bolds, so you'll have to
change that):

http://groups.google.com/[email protected]

Ahhhhh. Here's a version with the change:

Option Explicit
Sub testme01()

Dim myWords As Variant
Dim myInput As String
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

myInput = Trim(InputBox(Prompt:="enter words/phrases separated by commas"))
If myInput = "" Then
Exit Sub
Else
myWords = Split97(myInput, ",")
End If

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

For iCtr = LBound(myWords) To UBound(myWords)
'remove leading/trailing
myWords(iCtr) = Trim(myWords(iCtr))
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
With myCell.Characters(Start:=letCtr, _
Length:=Len(myWords(iCtr)))
.Font.FontStyle = "Bold"
.Font.ColorIndex = 3
End With
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

'from Tom Ogilvy
Function Split97(sStr As Variant, sdelim As String) As Variant
Split97 = Evaluate("{""" & _
Application.Substitute(sStr, sdelim, """,""") & """}")
End Function


Select the range first and run the macro.

If you're using xl2k or higher, you can remove the Split97 function (at the
bottom). But remember to change split97 to split in this line:

myWords = Split97(myInput, ",")
becomes
myWords = Split(myInput, ",")

(Split was added in xl2k.)

 

[Matt]

Thanks, that worked great!!!

-----Original Message-----
Here's a link to a routine that might help (it only bolds, so you'll have to
change that):

http://groups.google.com/groups? threadm=3F5F9D10.3144DF4B%40msn.com

Ahhhhh. Here's a version with the change:

Option Explicit
Sub testme01()

Dim myWords As Variant
Dim myInput As String
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))