VBA to automatically Hyperlink cell text with similar file name

[fowzan]

hi,
i currently have an excel sheet where i am entering the issued certificates for the jobs done.
Some examples for the certificate names are
JAN18/161/C1-C10
FEB18/185/C1
FEB18/190/C1 & FEB18/190/MPI/C1

I have similar names for the files that i save. But the problem is that as windows does not accept "/" in the file name i have replaced "/" with "." in the file names and they look like
JAN18.161.C1-C10.pdf
FEB18.185.C1.pdf
FEB18.190.C1 & FEB18.190.MPI.C1.pdf

It would be very difficult for me to link them manually, as there are alot of jobs. And there are not just certificates that i have to deal with. i have invoice, quotation, delivery notes with the same requirement.

Now i want to be able to click the cell with certificate name text and i want the file to open.
And i want the hyperlinks to update automatically as i put the certificate name. if there is no file with that name it should stay as plain text.
It would be easier if the color for the certificate name text can change when it gets hyperlinked so that it becomes easier to identify that it has been linked.

 

[fowzan]

anyone?

 

[fowzan]

i dont know if i am allowed tp post link to other forums, but maybe someone who is looking for a similar macro can benefit. i got the solution on an another forum.
https://www.excelforum.com/excel-pr...-text-with-similar-file-name.html#post4840186