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?

  • If you could provide a sample sheet, this would greatly help us figure out the specifics.
    – David Tan
    Apr 3, 2022 at 4:43

1 Answer 1


You could have something like this: highlight duplicate 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).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.