Find/replace with different text colour messes up

[mystp]

When doing a Find/Replace on a certain word that needs to have a different
colour than default - say, red - Excel incorrectly colours the whole cell
instead of just the word that was searched on.



To see this in action, try this:



1.. Open up a blank Excel sheet
2.. Enter some text in a few cells - "This is a test", for instance. Now,
let's try to use search/replace to colour only the word "test" in red.
3.. Open up Search/Replace
4.. On the "Search for"-line, enter: test
5.. On the "Replace with"-line, enter: test
6.. For the "Replace with:"-line, choose Format, then Formats, then select
the Font-pane and then choose a red colour under the Colours drop-down box.
7.. Click OK
8.. Now you should see a line saying "__ example __" in red in the
"Replace with:"-region, and we should technically be ready to replace the
word
"test" in the default colour with the same word in red.
9.. Click "Replace all" and watch what happens...


Instead of colouring just the word "test" in each cell, Excel has coloured
the entire cell!

Hopefully, this bug will be addressed in an upcoming patch... but until
then,
is there another way of colouring just a single word - without affecting
anything
else in the given cell - using find/replace?

Thanks,

- Asbjoern

 

[RagDyer]

I like that, but since I'm from out here in the sticks (boonies), what
version is this in?

 

[mystp]

This is in Excel 2003 (version 11.6355.6408 SP1 to be exact)

Kind regards,
- Asbjoern

 

[Dave Peterson]

Saved from a previous post (or two!):

If you want to change the color of just the characters, you need VBA in all
versions.

You want a macro????

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("widgets")

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.colorindex = 3
End If
Next cCtr
Next myCell
End If
Next iCtr
Application.ScreenUpdating = True

End Sub

This line:
myCell.Characters(Start:=cCtr, _
Length:=Len(myWords(iCtr))) _
.Font.colorindex = 3
changes the color.

 

[Guest]

I'm having the same problem. Only not with color. With Italics. If my
cell is a mixture of italics and regular font text and I do a find and
replace of a single word, it automatically changes the formatting to all
italics. Does anyone have a solution for these problems? I have searched
HELP and I have searched the Microsoft Excel and Microsoft Office web sites.
NOTHING is discussed on this formatting problem. HELPPPP!!!! Thanks.

 

[Dave Peterson]

When you tried modifying the macro that was in that same thread, did you have
trouble?

http://groups.google.co.uk/group/mi..._frm/thread/8764ea671f62ddea/0fd78c85ea1357be

or

http://tinyurl.com/drb72

 

[Guest]

Hi, Thanks for your response. I don't know how to do macros. I read your
instructions, which said VBA. I don't know what that means. I am
self-employed and use the Microsoft Office components for a lot of business
uses. Unfortunately I have not had time to learn basic programming. Is the
only way to fix this problem to write a macro? It is a flaw in the program.,
then? I thought maybe I just didn't understand how to use the program
properly. Joy

 

[Dave Peterson]

Nope. You did everything correctly. It's just the way excel works.

You have another macro response at your other post.

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

If this is important, it might be worth taking a little time to learn some VBA.

 

[Guest]

Dave, Thank you for your help. I have bookmarked the macro introduction you
suggested for another day. I am on a project deadline and can't take time
right now to learn programming. But now that I know that what I want to do
can't be done, I'll have to find another way or use another program. You've
been kind. It is appreceated. Joy

 

[Dave Peterson]

You may want to post followups to all your posts that you're working on your
solution.

No sense making others try to do the same work.

Dave, Thank you for your help. I have bookmarked the macro introduction you
suggested for another day. I am on a project deadline and can't take time
right now to learn programming. But now that I know that what I want to do
can't be done, I'll have to find another way or use another program. You've
been kind. It is appreceated. Joy

 

[Guest]

Will do. Thanks. Joy

You may want to post followups to all your posts that you're working on your
solution.

No sense making others try to do the same work.

 

[Guest]

Seems macros are the only way to go. Will learn macros now. Thanks. Joy