Add a substring to an existing hyperlink address using VBA

Dear Experts:

I would like to run a macro that performs the following task

Loop thru all the cells in Column F.
... As soon as the macro hits a grey shaded cell (RGB 250, 250, 250) ...
... go three cells to the left (Column C) ...
... and add the string '_copy' at the end of the stored hyperlink (but before the file extension)  that is stored in that cell

For example:

First grey cell in F 147
The hyperlink in C 147 has the following address : C:\myfolder\47-338-44-09.tif
The new hyperlink address after running the macro will be: C:\myfolder\47-338-44-09_copy.tif
That is all of the stored hyperlinks have the following make-up
C:\myfolder\##-###-##-##.tif

After running the macro the hyperlink addresses will say:
C:\myfolder\##-###-##-##_copy.tif

Help is much appreciated.

Thank you very much in advance.

Regards, Andreas
Andreas HermleTeam leaderAsked:
Who is Participating?
 
MichaelBusiness AnalystCommented:
Hi Andreas,

the following code is one approach to do this:

Sub addToHyperlink()
    Dim rng As Range, cell As Range
    Dim lr As Integer
    Dim strHyperlink As String
    
    lr = Cells(Rows.Count, 3).End(xlUp).Row
    Set rng = Range(Cells(1, 6), Cells(lr, 6))
    
    For Each cell In rng
        If cell.Interior.Color = RGB(250, 250, 250) Then
            strHyperlink = cell.Offset(, -3).Value
            strHyperlink = Mid(strHyperlink, 1, Len(strHyperlink) - 4) & "_copy" & Right(strHyperlink, 4)
            cell.Offset(, -3).Value = strHyperlink
        End If
    Next
End Sub

Open in new window


I hope this works for you. It assumes that every corresponding cell in column C of a grey cell in column F, contains a hyperlink. If not, it will throw in an error.

Remember to test the code on a copy of your workbook.

Joop
0
 
Andreas HermleTeam leaderAuthor Commented:
Hi Joop,

thank you very much for your swift help. I will test the code tomorrow and let you know.

Again, thank you very much for your professional help.

Regards, Andreas
0
 
Andreas HermleTeam leaderAuthor Commented:
Hi Joop,

works like a charm, thank you very much for your great and professional support.

Regards, Andreas
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.