conditional format external links

  • Thread starter Thread starter Henk van Walbeek
  • Start date Start date
H

Henk van Walbeek

Hello is there a way to give all external links a colour via "conditional
formatting".

I know that Findlink.xla can show them and make a list. But is it also
possible to mark them ?

Regards

Henk
 
Maybe you can get close (or as exact as you want):

When you have an external link, the formula looks a bit like:

=[book4.xls]Sheet1!$D$9

You could search that formula for [ or ]. If you find them, it _may_ have
external links.
(But if you use [] in your formulas (as text), then this ain't gonna work
nicely. (Maybe searching for .xls would be worth adding!)

Anyway, you could create a userdefined function to look through the formula:

Option Explicit
Function HasExternalLinks(rng As Range) As Boolean

HasExternalLinks = False
With rng(1)
If rng.HasFormula Then
If InStr(1, .Formula, "[") > 0 _
And InStr(1, .Formula, "]") > 0 _
And InStr(1, .Formula, ".xls", vbTextCompare) > 0 Then
'good chance it has external links!
HasExternalLinks = True
End If
End If
End With
End Function

Then use a Conditional formatting formula of:
=hasexternallinks(a1)

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Short course:
Start a new workbook
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel.

Add the Conditional formatting and see if it suffices.

(You can add as much as you want to make it sensitive enough for you.)
 
Back
Top