Find all and replace based on a named range

[klturi421]

As the title suggests, I would like to find and replace all items on 'Sheet1' based on a named range (dubbed "codes") from 'Sheet2'. See the example tables below for clarification.

Current Format for Sheet1 and Sheet 2:

Sheet1	Sheet2 - Named Range
Management | Repair | All Services | Dispatch	Management | Repair |
Management | Upgrade | All Services | Dispatch	Management | Upgrade |
Management | Downgrade| Pest | Dispatch	Management | Downgrade|
Management | Cancel | Lawn | Dispatch	Management | Cancel |

Example of what Sheet 1 Would look like once the 'find all and replace' macro has completed:

Sheet1

All Services | Dispatch

All Services | Dispatch

Pest | Dispatch

Lawn | Dispatch

Any ideas how I can get something like this to work?

[rcm]

try this macro

[klturi421]

try this macro

That gets what I need half way there. The only issue with the macro you provided is that it only does a like for like. Meaning it only checks removes A2 on sheet1 from a2 on sheet2. What I would need would be something along the lines of it checking the list for any of those items. The list that I am looking to work with will be in excess of 20+ items and definitely wont be in any specific order. I'll upload a better example here in a few minutes. Just need to set up a workbook.

Edit:
Here's the workbook with the example that I am referring to. Book2.xlsx

[rcm]

Sorry, I read it to lightly, but here it is

Please Login or Register  to view this content.