EXCEL Workbook Conditional Formatting

Question

I have a workbook with multiple sheets (90+)

One of the sheets has a column of text that I want to highlight each entry if a match to the text appears on any other sheet in the workbook.

Is there a way to accomplish this fairly easily?

Answer 1

Thank you for the suggestions. So my many sheets all had specific data in just one column per sheet. What I ended up doing is creating a reference sheet for the master and created formulas to copy the specific columns from all the other sheets to the reference sheet. I then built my conditional formatting to highlight the values on the master from the reference.

Thanks for the idea though!!

Answer 2

Considering Lui's solution:

Which sheet has the master list? And in what column? Are you checking every cell of every other sheet for each of the entries in the master list? In other word do you want to check all 1,048,576 rows x 16,384 columns which is 17,179,869,184 on each of the ~90 sheets?

Answer 3

Hi Ben Knaup，

Thank you for your question in this community, I am willing to help you solve your problem, according to your description of the problem,

Do you want a column of text in your Excel, and the same text in other sheets, highlight them all,

If I understand correctly, there may not be a convenient way to meet your needs in Excel's built-in functions. I suggest you try to achieve your needs through VBA:

You can refer to the following article:

Is it possible to search across entire workbook for duplicates as cell is entered?

Disclaimer: Microsoft provides no assurances and/or warranties, implied or otherwise, and is not responsible for the information you receive from the third-party linked sites or any support related to technology. ​​

If I misunderstood you can provide more information for our reference！

Best Regards，

Ethan