[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More


Automated Link Checker

Published on
5,893 Points
1 Endorsement
Last Modified:
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlighted yellow and are converted into a hyperlink.
Automation and link building do not belong in the same sentence, or do they? Some tasks involved in great link building can be tedious. Why not make your life a little easier? Part of resource link building involves reaching out to a lot of sites and offering an asset with a link back to your client. Every once in awhile, a site will put up your link and never tell you. No problem, you can run a backlink check and see any new links your client has obtained. But how do you sort out the links that you obtained through your own efforts?  
Well you would take your list of domain URL’s that you have reached out to and dump them into an Excel file. Then you would download any referring domain backlinks and add these to your list of URL’s that you have reached out to. 
Then type ALT+F8 and hit Enter and your work is done (if you used my code). 
Over the years I have spent a lot of time learning Excel, and how to automate projects. If there is a task that I do regularly, I try and automate what I can. Time is money, efficiency is key. Of course, some things should never be automated. Part of great link building involves building relationships with other sites. You are much more likely to build a relationship as yourself versus an automated robot. 
Use this script to simplify your link checking and save some time. This code will take your list of outreached sites combined with your client backlinks and look to see if you have gained any links through your outreach efforts. This is done by looking for URL’s that are duplicated in your list, suggesting that a site you outreached is showing a backlink to your client as well. 
The code takes your list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlighted yellow and are converted into a hyperlink for ease of use. 
How to get the code into Excel. 
Open up an Excel workbook.
Select the “File” tab. 
Select “Options” at the bottom of the File menu. 
The Excel Options window will appear, select “Customize Ribbon”. 
Check the “Developer” box under Main Tabs. 
The Developer tab will now appear in all of your Excel workbooks. 
Select the “Developer” tab, then select “Visual Basic” on the very left. Alternatively, you can type ALT+F11. A new window will open up, Visual Basic Editor (VBE). 
In the Project Tree, right-click VBAProject (PERSONAL.XLSB). 
Select Insert, then Module. 
Copy the code in this article and paste into the Module window. 
Click the “Save” icon or type CTRL+S to save the code into your Personal Workbook.
Close out the Visual Basic Editor.
Populate Column “A” with your list of domain URL’s that you have already outreached. Note that these URL’s are domain level. It is important that all URL’s in this list are domain level, so that duplicate values can be found. 
Run a backlink report on the client in question. Type your client URL in the Site Explorer. 
Click “Referring domains” in the menu on the left. 
Click “Export” on the right side. A new window will popup, select the “Microsoft Excel” radio button and click “Start Export”. 
Open up the workbook you just downloaded. In the “B” column, all Fresh domain level backlinks will be listed. Click the “B2” cell and hold down CTRL+SHIFT+DOWN to select all the URL’s in the “B” column. Then hold down CTRL+C to copy the selected cells. 
Now that you have the backlinks copied, close the workbook. Open up the Excel workbook with your already outreached URL’s. Select the first empty cell in column “A” and paste your backlinks by holding CTRL+V. 
Hold down ALT+F8 to open up the Macro window. Select PERSONAL.XLSB!HighlightDuplicateLinks and click “Run”. 
A new window titled “Look for Duplicates” will open. Click OK. 
Once script is completed, (a few seconds depending on the amount of URL’s), click OK. 
If you have sites that you outreached that did indeed link back to your client, they will appear at the top of the list in yellow.  
Your work is done and you can easily see if you have any additional links to report to your client. 
Anytime you have a list of URL’s that you want to check, simply type ALT+F8 and click Run on the selected macro. 
Copy the code below: 
Sub HighlightDuplicateLinks()

'Takes URL's in Column A, sorts and highlights duplicate values.

Dim dialogBox
Dim finishBox

   dialogBox = MsgBox("Look for Duplicate Values in Column A?" & _
   vbNewLine & vbNewLine & "Written by Joshua Snow" & _
   vbNewLine & "jsnow.pageonepower@gmail.com", vbOKCancel, "Look for Duplicates")
   If dialogBox = vbCancel Then
   Exit Sub
   End If

Dim lastRow As Long
Dim duplicateFound As Long
Dim i As Long

Dim myWB As Workbook
Set myWB = ActiveWorkbook

Dim ws As Worksheet
Set ws = myWB.ActiveSheet

Dim cell As Range

ws.Range("A1").Value = "URL List"

lastRow = Cells(Rows.Count, "A").End(xlUp).Row

For i = 1 To lastRow

If Cells(i, 1) <> "" Then
   duplicateFound = WorksheetFunction.Match(Cells(i, 1), Range _
       ("A1:A" & lastRow), 0)

If i <> duplicateFound Then
   Cells(i, 1).Interior.Color = rgbYellow

End If
End If


   AutoFilterMode = False
With ws
   .AutoFilter.Sort.SortFields.Add(Range _
   ("A:A"), xlSortOnCellColor, xlAscending, xlSortNormal) _
       .SortOnValue.Color = rgbYellow
   .AutoFilter.Sort.Header = xlYes
   .AutoFilter.Sort.MatchCase = False
   .AutoFilter.Sort.Orientation = xlTopToBottom
   .AutoFilter.Sort.SortMethod = xlPinYin

   .Columns("A:A").ColumnWidth = 50 'for aesthetics
End With

   For Each cell In Range("A:A")
       If cell.Interior.Color = Excel.XlRgbColor.rgbYellow Then
           cell.Value = "http://www." & cell.Value
           ws.Hyperlinks.Add Anchor:=cell, Address:=cell.Formula
       End If
   Next cell

finishBox = MsgBox("Finished!" & vbNewLine & vbNewLine _
   & "Duplicate URL's are at the top of the list.", _
   vbOKOnly, "Finished")

   If finishBox = vbokay Then
   Exit Sub
   End If

End Sub

Open in new window


While automation is not advised for the majority of link building, certain tasks can be automated and every minute adds up. Integrating customer data, prospecting sites and outreach emails are great candidates for some sort of automation. Even a little automation can save you a ton of time and increase your business' profits. For example, let’s say that you are able to find duplicate sites without using this code in about 2 minutes and that you have ten clients that you check for unreported links every month. Using this code will save you about 20 minutes of work every month or 4 hours a year. Now let’s assume that an average employer cost for link building is $15 an hour. Using this code you could save your employer $60 a year. Think big and imagine that same employer has 50 link builders all performing the same task each month. This code will now save your employer $3,000 a year. There is always room for a little automation to save time and money, even in the world of high quality organic link building.
Author:Josh Snow
1 Comment

Featured Post

Amazon Web Services

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
A query can call a function, and a function can call Excel, even though we are in Access. This is Part 2, and steps you through the VBA that "wraps" Excel functionality so we can use its worksheet functions in Access. The declaration statement de…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month