Search replace Excel XP

[richiwatts]

I am using the search and repalce function in Excel XP

With the words that i am repalces I also wantr them to be GREEN so tha
I can see the changes clearly.

The problem is that it changes the colour of all the text in any cel
that contains the word that i have changed.

Is there any way to only change the colour of the word that has bee
changed?

Also is it not possible to only change whole words so that searchin
for

Shop

will not change words like

Shopping

Rich

 

[Dave Peterson]

Excel doesn't make much of a word processor.

If you can lower your expectations a bit, maybe this link will help:

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

It's a routine that bolds some text.

You could modify this line:
..Font.FontStyle = "Bold"
to
..Font.colorindex = 6 ' or whatever Green is.


The bad news is it doesn't look for whole words--that kind of search isn't built
into excel. I've seen some posts that use regular expressions to look for this
kind of stuff. You may want to search google if it's really important.

If you decide to use the code (try it against a copy of your worksheet!), do all
your Edit|Replaces, then change the code:

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

to match your list of words that got changed.

===
And since whole word replacements aren't built into xl, I just use a combination
of Replace's and "find next's" to skip the cells I don't want modified.