See if this helps. Right click on your sheet tab holding the hyperlink, left click on View Code, and paste the following procedure into the large white area that is the worksheet module. Press Alt+Q to return to the worksheet.
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
MsgBox "Your macro goes here"
End Sub
A workbook-wide event for this can be placed in the workbook module if you want a macro to be run for any hyperlink clicked. The code that is
Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink)
MsgBox "Your macro goes here"
End Sub
In either case, replace
MsgBox "Your macro goes here"
with whatever the essence of your macro code really is.