I have 1490 hyperlinks in Excel that I need to identify as either being good or bad. These are all hyperlinks to PDF documents. An example of the file name is "C:\Users\Jennifer\Docs\Lease\12345-Lease.pdf". There are 1490 rows with this type of data in one column. Then in another column I have "=HYPERLINK(C4,"LEASE"). I would like to be able to highlight the bad hyperlinks in yellow. I assume a macro is the way to go but I can't figure it out....
Thanks!
What do you mean by 'bad hyperlinks'
I am assuming you mean invalid or non existent.
You will probably have to add a macro to check if the folder and filename exist, something like that.
---
Hans
"IT" Always crosses your path!
May the (vba) code be with you... if it isn't; start debugging!
If you like my answer, Click the * below to say thank-you
Yes - sorry for the confusion. When I click on the hyperlink I sometimes get an error message "Cannot open the specified file." So, you are correct in that I need to be able to check if the filename exists and then highlight the ones that do not.
please see comment below - I responded incorrectly.
I would write a user defined function (a macro that returns a value or a true/false and us a right-click event that checks if the file exists, if it's incorrect then do nothing thus avoiding the error.
thank you but wouldn't i have to right click on every file name? i have 1,400 file names.
You can add the UDF (User Defined Function) to a worksheet event, like worksheet_Activate and have the macro run and color the background red if it's incorrect.
You can also use the UDF in a conditional format rule to change the color if the result is invalid, There are many roads the lead to Rome.
It's bedtime her, I'm going off-line, but post and ask. I'll read it tomorrow
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks