Excel Macro: Comparing and highlighting the common texts which may not be exact match

[bhangale0905]

Guys, I am an insurance underwriter and prepare Insurance quotations. Number of these in a day.

I receive a RFP/RFQ from broker and basis which I prepare the quotes.

Attached excel sheet is the simplified version to explain the point where I need to automate by using formula. And if not by Formula, by using a VBA Macro.

One sheet shows a terms in RFP and other shows Terms in my Quote. For simplicity, I have put them together in one workbook side by side.

• What I want to achieve here is for excel to check the term in RFP and if the match is found in a terms listed in Quote, excel to highlight the terms in Quote Sheet as well as in RFP Sheet. For example, in RFP sheet there is a term “Obsolete Parts”. I want excel to highlight “Obsolete Parts” in sheet RFP as well as Sheet Quote.
• Second challenge can, be not all the terms matches exactly. For example, in RFP, there is a term “Escalation @ 10 %” but in Quote sheet there is term “Escalation”. I want excel to highlight both the terms separately in respective sheets. There are many such cases in attached example sheet.
• The terms which is absolutely unique need to remain un highlighted. For Example, in sheet RFP term “Reinstatement Value Clause” is unique and should remain un highlighted. Similarly, in sheet Quote, the term “Forest Fire” is unique and should remain un highlighted.

Request help in finding solution.

Sheet "RFP"

Impact Damage due to Insured’s own Rail/Road Vehicles, Fork lifts, Cranes, Stackers and the like and articles dropped there from Omission to Insure additions, alteration or extensions @ 5% Escalation @ 10 % Insurance Of Additional Expenses of Rent For An Alternative Accommodation(For non Manufacturing Risks and can be applicable for tenant / owner occupant) Claims preparations costs Obsolete Parts Waiver of Under Insurance up to 15% -As per IAR Tariff Architects, Surveyors, Consulting Engg Fees above 3% of claim amount Debris Removal above 1% of claim amount Capital Addition Clause Reinstatement Value Clause

Sheet "Quote"

Architects Surveyors Fees Removal of Debris Deterioration of stocks Forest Fire Impact Damage due to Insured’s own Vehicles Omission to Insure Additional Expenses of Rent for alternate accomodation Escalation Claim Preparation Cost (Excluding appointment of experts) Waiver of Under Insurance Capital Additions Obsolete Parts