Automated Link Checker

Josh SnowNetwork Engineer
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 AL T+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 & "", 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.

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.