Macro to edit hyperlink text to display

I am looking for a macro that will edit a hyperlink if the text to display changes based on the criteria in the cell. The cell reference will return information from the text to display. Example: The hyperlink text to display in cell A1 may say John but if the name changes to Mary the macro will edit the hyperlink text to display to say Mary. There are over 100 hyperlinks and I do not want to manually edit the hyperlinks each time a cell value changes. Has anyone found a way to edit a hyperlink like this through a macro.

Thank you

I am trying to post the excel file so you can view what I am doing but it will not upload.

The file did post. If you open it up and click on the link under unit name, i.e. Syracuse, the link will take you to a page with the Syracuse information on it. If the name Syracuse is exchanged with another name, I want the other name information to come up but I want a macro to be able to edit the link without me having to manually do control K.

Maybe this is what you are looking for:

Vlookup and create a hyperlink to the first match
When working with large datasets, you may often find yourself in a situation when you need to look up a specific value and return the corresponding data from another column. For this, you use either the VLOOKUP function or a more powerful INDEX MATCH combination.

But what if you not only want to pull a matching value but also jump to the position of that value in the source dataset to have a look at other details in the same row? This can be done by using the Excel HYPERLINK function with some help from CELL, INDEX and MATCH.

The generic formula to make a hyperlink to the first match is as follows:

Please Login or Register  to view this content.

This is the Link to the full artical:
https://www.ablebits.com/office-addi...link-function/

I took that worksheet out so that part of the code can be deleted, however the rest of the code works fine as it just passes over that part of the code. someone gave me a formula but it does not work for what I need as I need to edit the hyperlinks when the cell criteria changes. I know this is difficult as I have search high and low to try and find what I am looking for to no success. When I try and form it as a macro to edit, the macro will not record an edit to a hyperlink.

Yes, if you click on each unit name like listed below, the hyperlink will take you to the trend chart and the name will change based on the name you click on. These name change as the sheet is updated and I have to manually edit the hyperlinks to change the text to display name. I am trying to find a way automate this task.


Unit Name
SYRACUSE PO
TROY PO
SYR-FRANKLIN SQUARE S
WATERTOWN PO
ALB-GMF STORE
SYR-COLVIN STA
ALB-CARRIER ANX
LATHAM PO
LIVERPOOL PO
GLENS FALLS PO

Awesome, you are truly a genius. Thank you for taking the time to find an excellent solution to my problem.