How to highlight duplicate values when clicking on a specific cell

Question

I'm new to spreadsheets, but trying to put a functional one together for my team.

I have a list of member names, and several lists of tasks they are assigned on different days of the week. The goal is to click on the cell in the list containing their name, and it highlights all the places where their name appears in the rest of the spreadsheet.

Example: when Joe Doe wants to know what assignments he has for the month, he clicks on his name in the list of names, and all the places where "Joe Doe" appears in the spreadsheet get highlighted. Optional: each name in the names list gets a different color assigned them, so their duplicate values get highlighted in that color.

I've tried to find a conditional formatting or a custom string to accomplish this, but most that I find are merely a one-time-only version of "find and replace" for the whole document, not tied to a specific cell when it's clicked.

Am I making things too complicated?

Answer 1

You could have something like this: When you click a name of the list in column A you'll get a different color for each name.

See the test sheet

Note that you'll need to RUN and then AUTHORIZE the script involved before you can see how it works (first time only).