Function CreateLink() As Integer
Dim row As Integer 'used to track the row we're in
Dim strLink As String 'used to store the URL that already exists as text in a cell
Dim column As String 'used to track the column we're in
Dim Cell As String 'used to combine the row and column into a cell reference value
Dim displayTxt As String 'used to set the text we want the hyperlink to display
displayTxt = "IMAGE" 'hyperlink will always show up as "IMAGE"
column = "G" 'existing text hyperlink values are all in column G
row = 2 'I had a header row so actual values start in row 2
Cell = column & row 'combine row and column in this case to get the Cell = "G2"
While Range(Cell).Value <> "" 'continue looping until the value in the cell is blank
strLink = Range(Cell).Value 'set the hyperlink value to be what's in the cell (G2 first time)
Range(Cell).Hyperlinks.Add _ 'add the hyperlink
Anchor:=Cells(row, column), _ 'sets which cell to add the link in
Address:=strLink, _ 'sets the hyperlink URL
TextToDisplay:=displayTxt 'sets the text to display. We wanted "IMAGE" only
row = row + 1 'go to the next row in the next iteration of the loop
Cell = column & row 'set the new cell value with the new row number
In our case, the url text existed in column "G", which is why I was able to set it statically above. Then I wanted to loop through the sheet to get through all the cells in the G column until there weren't any more values, which is where the While loop comes in. As I looped through the cells, I also wanted to convert the value within the cell to be a clickable hyperlink using the text that was already in the cell, and also change the visible text in the cell to read "IMAGE" so that's all the person would see.
I just put a call to this function in the SelectionChange portion of the worksheet properties. After it ran and converted the cells, I removed the VB code and saved the worksheet again. This ran pretty quickly and saved the user from manually going in and changing the display text on all the cells, and from changing the plain text representing the hyperlink to an actual hyperlink