Creating a macro to highlight cells in one sheet using info provided in another sheet

[bfdrahul]

I have an excel file which consists of 50 sheets. 49 of these sheets are identical to one another, although each of them have a different sheet name to the other. The first sheet however is different to these other 49 sheets.

First sheet information:
The first sheet is named ?Acquirer Listings? and contains a list of acquirer listings. This sheet contains nine columns that are in the following order:
URL
Name
Sheet Name
Profile
Business categories
location
equity
accredited
listed
Valuation budget (Minimum)

This sheet contains 398 rows of acquirer listings that showcase information about each acquirer with respect to each column.

Other sheet information:
The other sheets are each named after an Acquirer within the ?Acquirer Listings? sheet. For example, an acquirer with the sheet name ?Acquirer2? will have their own sheet with the sheet name ?Acquirer2?

The other sheets each contain a table of acquirer preferences. These preferences are made up of four categories (Category, Valuation Budget, Location, Stake). Each of these categories offer a selection of options in the following format:

Category:
SaaS
eCommerce
Mobile
Content
Marketplace
Agency
Fintech
Web3

Valuation Budget:
$100,000
$1,000,000
$10,000,000

Location:
EMEA
AMER
APAC

Stake:
Full
Minority
Majority



Based on the information listed within the columns for each acquirer in the ?Acquirer Listings? sheet, how should I write my Excel VBA code to write a macro which will highlight the relevant options within each respective acquirer preferences sheets?

For example, imagine that there is an acquirer with the sheet name ?Acquirer5? that has ?SaaS*Marketplace? within its ?Category? column, ?AMER? within its ?Location? column, ?Full Majority Minority? within its ?Equity? column, and ?$100,000? within its valuation budget column. This macro should be able to open the sheet named ?Acquirer5? and highlight the cells which read ?SaaS? and ?Marketplace? under the ?Category? column, highlight the cell which reads ?AMER? under the ?Location? column, highlight the cells which read ?Full?, ?Majority? and ?Minority? under the ?Equity? column, and highlight the cell which reads ?$100,000? under the ?Valuation Budget? column.

For reference, I've attached a dummy file to this query that may make this question easier to understand.

[p45cal]

In the attached, in the Acquirer Listings sheet, right-click on any cell in column C.
If the sheet name exists it should colour the cells on that sheet and make that sheet active.
It's flaky.
The categories in column E are separated with a hard space (ascii 160), not a normal space
Fintech in cell M9 of all the acquirer sheets is followed by a space.

[p45cal]

Oh groan, cross posted here: https://www.excelforum.com/excel-pro...her-sheet.html
Provide links when you cross post!

[Aussiebear]

You are right P45cal, it's very disappointing to see this behaviour still ongoing. Perhaps bfdrahul will apologise.... only time will tell.

[bfdrahul]

Thank you so much!

[bfdrahul]

Apologies, still pretty new to the forum. Appreciate your advice!