Automated hyperlinks

Maybe this macro will help.

Sub Refresh()
For N = 1 To ActiveSheet.Hyperlinks.Count
    If Dir(ThisWorkbook.Path & "\" & ActiveSheet.Hyperlinks(N).Address) = "" Then
        ActiveSheet.Hyperlinks(N).Range.Font.ColorIndex = 3
    Else
        ActiveSheet.Hyperlinks(N).Range.Font.ColorIndex = 4
    End If
Next N
End Sub