Find and replace in Excel

-
Hi,

This question is related to Excel 365.

I have Sheet1 that has two columns. I Just gave a sample of word abbreviations. It has around 800.

Word abrreviation

Sub Blocky sbblky
Sub Fiss sbfiss
Grey gry
Red rd
Amorphous amor
.....

I have Sheet2 with the following in Cell A1 .. A100

medium grey, occasionally olive grey to dark olive grey, soft to sub firm, sub blocky , rare amorphous, grading to CALSISILTITE in part, trace lithic specks.

light olive grey to light grey, trace light brownish grey, predominantly firm to occasionally hard, sub blocky, trace amorphous, grading to MARL in part.

medium grey, occasionally olive grey to dark olive grey, soft to sub firm, sub blocky , rare amorphous, grading to CALSISILTITE in part, trace lithic and carbonaceous specks, trace microfossils.

light olive grey to light grey, trace light brownish grey, predominantly firm to occasionally hard, sub blocky, trace amorphous, trace microfossils.

dominantly light to medium grey, occasionally olive grey to dark olive grey, soft to sub firm, sub blocky to amorphous, trace lithic and carbonaceous specks, trace microfossils.

light olive grey to light grey, trace light brownish grey, soft to firm, sub blocky, trace amorphous, trace microfossils.

My question is to find each word from Sheet2 and if it exists in Word it should be replaced with corresponding words in column2. If the word does not exists, it should be changed to color red. I am sure it is possible with a Macro. I am very new to Macro. Your help in this regard will be highly appreciated.


Nehru
See more 

Your reply

1 reply

Posts
2435
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 4, 2018
0
Thank you
Hi Nehru,

Give the following code a try:
Sub RunMe()
For Each cell In Sheets("Sheet1").Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
    Sheets("Sheet2").Columns(1).Replace What:=cell.Value, Replacement:=cell.Offset(0, 1).Value, LookAt:=xlPart
Next cell
End Sub


NOTE: The word "red" is also part of the word "predominantly". To prevent the "e" to be removed from the word "predominantly", place a space in front of the word "red" on sheet1. So "Red" becomes " Red". Keep this in mind with other short words you might have in your list.

Best regards,
Trowa
Respond to TrowaD