I reopened this thread to get a tweak to the solution. Currently, the matched names are displayed in a separate column. Because the original data is linked to their respective sheets and has conditional formatting to change the colors, It would be better if the column being searched which is "A", actually displays the matching results so the links and colors are maintained with the search results. There are over 500 individual cells in column "A" to be searched so it is important to maintain the links to the sheets in the search results to easily navigate back and forth.
EDIT: The list in column "A" is created dynamically so whatever formula or VBA is used to generate the search needs to be dynamic as well.
Bookmarks