VBA to create hyperlink on each cell which display detail of other sheet

[abraham123]

Hi All,

I need to check all the details in master sheet when clicking on any ID from child sheet through hyperlinking (or any alternative). I am able to do manually for only one row but can not proceed when 1000 of row are there in the child sheet. Doing manually will take lots of time. Is there any alternative approach or developing a macro can automate this process.

e.g. from the attached sheet, when I click on value '92315' from child sheet, it should select the entire row from master sheet when matching ID. If not match, it should say 'not matched'

[Bernie Deitrick]

You don't need to hyperlink all the cells - you can use VBA code to find and select the row of interest.

1) Copy this code.
2) Right-Click the sheet tab of "Child".
3) Select "View Code"
4) Paste the code into the window that appears.
5) Save the file as a macro-enabled .xlsm file.
6) Make changes as needed

Please Login or Register  to view this content.

[abraham123]

Thank you Bernie for the solution. Its working fine except blank cell.
When I am clicking on the blank cell, it is selecting the empty cell in next sheet. Is it possible to restrict it.

[Bernie Deitrick]

Why would you click on a blank cell?

Change

Please Login or Register  to view this content.

to include

Please Login or Register  to view this content.

[abraham123]

The macro is working for partial match only.
e.g. if ID '5121' present in child sheet, then it highlights the ID '512101' available in master sheet but not the exact '5121' ID.
Could you please help me how to find for exact match.
Uploaded the sheet for your reference.

[Artik]

The problem with using the SelectionChange event is that you cannot select the selected cell without moving to another sheet. Admittedly, you can select multiple cells starting with the cell you are interested in (e.g. for editing).
I went in the direction of creating hyperlinks. For this solution I say they are fake hyperlinks because they pointed to themselves. But for this you can use the FollowHyperlink event. The whole thing in the attachment.

Artik

[Bernie Deitrick]

Replace

Please Login or Register  to view this content.

with

Please Login or Register  to view this content.

But, ultimately, you should use an ID system that follows specific rules that don't allow that sort of error: e.g., all telephone numbers are 10 digits - not 9, not 11 - exactly 10. So you should not have 5121 as an ID if 512101 is also an ID.

If selecting the cells is an issue, you can either change from the selection event to the BeforeDoubleClick event, or you can turn events on and off as needed, using simple macros:

Please Login or Register  to view this content.

[abraham123]

Thank you Bernie and Artik for the solution.