Excel 2010 - Highlight dead or broken links to files (pdf, jpg, etc.) and network folders

I am pretty good with excel, but know nothing about VBA or using macros. I have seen similar posts, but because I know so little, I don't know how to make them work for me.

I have several files where different pieces of data in the file are hyperlinked to a network folder (such as a customer's name in a list hyperlinked to the customer's folder) or a file on the network (such as a date hyperlinked to a pdf file of a document with that expiration date). I want to be able to highlight in purple (just to be sure I'm using a different color than anything else in the sheet) any links that are dead or broken on all sheets of the workbook.

Most of the time the issue is that the file has been moved or renamed, so when the link is clicked I get the error "Cannot open specified file.". I just want to be able to identify those hyperlinks without having to actually click every one. Then I can correct them as needed.

I'm hoping for something that doesn't actually open every file, as that would be a show stopper because there are so many links. And, I'm hoping for something that would work in any excel file, because I don't know enough to change the VBA every time.

TYSM! It works, but I'm curious about a couple things...because I'm not familiar with macros.

I can paste the code in, save it, and run it while the workbook is still open. But, if I close and reopen the file, the macro is not there. Am I doing something wrong? And is there a way to make it available for all workbooks I open?

You're awesome! I got it working. I thought about running on open, but I'm thinking I'll see how it works for a little while.

You're still awesome, but I spoke too soon on it working perfectly. I tried it on a different file, and it colored every cell with a link, but the links are working....?

I don't know enough. It didn't color every link, but almost every one. It did go through several sheets...could it be something to do with being on a network? I can run the file and then click on one of the links and it goes right to it.

Sorry I meant to say run the macro instead of run the file.

I tried the debugger and it showed several of the paths that were correct, so that looked fine. Just to see, I tried the macro from a similar posting...

Sub TestHLinkValidity()

Dim rRng As Range
Dim fsoFSO As Object
Dim strFullPath As String
Dim cCell As Range

Set fsoFSO = CreateObject("Scripting.FileSystemObject")
Set rRng = Selection
For Each cCell In rRng.Cells
If cCell.Hyperlinks.Count > 0 Then
strFullPath = ActiveWorkbook.Path & "\" & cCell.Hyperlinks(1).Address
If fsoFSO.FolderExists(strFullPath) = False Then
cCell.Interior.ColorIndex = 3
Else
cCell.Interior.ColorIndex = 0
End If
End If
Next cCell
End Sub

Yours is definitely faster. But it did the same thing. It marked everything with a hyperlink, both to files and network folders even if the links worked. The only links it did not highlight were cells with conditional formatting.

After running it, it did list every link as bad. It isn't listing the full path. Instead of listing it as I would think:

For a file: Drive:\Folder Level 1\Folder Level 2\filename.pdf

Or for a folder: Drive\Folder Level 1\Folder Level 2

It's instead just listing - Folder Level 2\filename.pdf on the files, and just the folder name, like Folder Level 2 - on the ones to folders.

Then for some links that may not have been updated recently, it just says:

...\Folder Level 2\Folder Level 3\filename.pdf

So, I'm not sure if that's allowing it to really check the path. Does that make sense?

There are quite a few email addresses in the file, so they just show as mailto: email address. I really wouldn't care if it just skipped those, because it's not really going to check the addresses anyway, so it really doesn't need to worry about them.

After doing some research, it appears that the first file I tried the code on that lists the results, was using a starting folder for hyperlinks. So it was listing relative links. That's why the whole link didn't appear. So, that's a whole other mess.

But, I found another file that does not have a starting folder. It listed all the links that were to specific files and that were working, in the Good column. But it listed all the links to a folder in the Bad column whether they worked or not.