Find and Replace with Changing finds and replacements

[wouter_van_overfelt]

Hello,

I want to make a number of strings uniform. These strings are all
located in the same column in a worksheet. The possible replacements
are all located in another worksheet of the same workbook. If and how
the string is changed is dependent on the string being searched. After
doing the work I want to know which replacing words were effectively
used (and color the cells in which they appear)

Here is an example to make things clear (the example has no relation
with the problem I want to solve and is just to make things clear
without bothering you with my problem):


Let's say I want all English animal names that appear in a particular
cell to be replaced with their French counterpart which should always
be singular.

e.g. worksheet 1 column A contains
A1: one Dog
A2: Twenty Dogs
A3: One Cat
A4: Twenty Cats
A5: one dog
And so on

Worksheet 2 column A through C contain:
A1: Chien (French for dog)
A2: Dog
A3: Dogs
B1: Chat (French for Cat)
B2: Cat
B3: Cats
B4: Catt
C1: Vache (French for Cow)
C2: Cow
C3: Cows
And so on ...

So Dog, Dogs and dog should be replaced with "Chien", Cat and Cats
should be replaced with "Chat". In worksheet 2 Cells A2, A3, B2 and B3
should be colored because these words were replaced while the Cells B4,
C2 and C3 must not be colored because we never replaced "Cow" or "Cows"
by "Vache" nor did we replace "Catt" by "Chat".

My own suggestion is (without having an idea how to color the cells
when a replacement is made):

dim r_WS1 as integer ' The Row in worksheet 1
dim r_WS2 as integer ' The Row in worksheet 2
dim c_WS2 as integer 'The Column in Worksheet 2
dim My_replacement as range

dim r_WS1 = 0
Do
r_WS1 = r_WS1+1
worksheets(1).cells(1,256) = worksheets(1).cells(r_WS1,1)
'Put every string in the first cell of the last column

For c_WS2 = 1 to 3
r_WS2 = 1
Do
r_WS2 = r_WS2 + 1
My_replacement = worksheets(2).cells(1, c_WS2)
' The replacement is always in the first row of the respective column!

Worksheets(1).Cells(1, 256).Replace _
what:=.Cells(r_WS2, cWS2), _
replacement:=My_replacement, lookat:=xlPart

' !!!!! IF A replacement is actually made, I want to color
My_replacement = worksheets(2).cells(1, c_WS2)
My Question is how can I check a replacement is made so I can color the
cell
' !!!!!

Loop Until IsEmpty(worksheets(2).Cells(r_WS2+1, c_WS2))
Next c_WS2
Loop until IsEmpty(worksheets(1).cells(r_WS1 + 2, 1)

Note that as far as I know I cannot use the worksheet event :
"worksheet_Change(ByVal Target As Range)" because the workbook listing
the initial strings will be
created out of another workbook. When I try to program the VBE from the
initial workbook containing the original data to include the worksheet
event in the second workbook, excel closes down for no good reason.

I really hope someone can help solve my problem.

Thank you,
Wouter