Find and replace in ExcelClosed

Question

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

TrowaD · Answer

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

Find and replace in Excel

1 reply

Similar discussions

Subscribe To Our Newsletter!