Automated Link Checker

Josh SnowNetwork Engineer
Published:
Updated:
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). 
tidg050puCuZGaFvyK-fLY3SS64FaqjXAHB0jV60 
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. 
QMxNriU2b65svV26LsYqFjFfhc_U6s9uqHVa1IGm 
Select “Options” at the bottom of the File menu. 
pTi6yoUSwrX_i041Fv720ZtPZiogpvNTR-n2GS2h 
The Excel Options window will appear, select “Customize Ribbon”. 
dxXuqb-7pFcI015iIQzYCljBLUb6BFwzU1VaB2Xd 
Check the “Developer” box under Main Tabs. 
ctWEnqYwTmDSZPM5tD80L4SdDbd81-jc_jLq14p9 
The Developer tab will now appear in all of your Excel workbooks. 
JAt23QDTRnno3F0eStubBoP1tZiuqCYDtatV4lW4 
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). 
gdFgyW-auCGIWdFNRvGGWiLRWjiip6Uiu-xhUQAr 
In the Project Tree, right-click VBAProject (PERSONAL.XLSB). 
mgBKPT1DrpBkzjW3R2_agsxCRWUUuL1F9FaI5_mU 
Select Insert, then Module. 
qjTTUej1ct4pJDEV9yYQmpWN2lB3ZN3AI2focqIM 
Copy the code in this article and paste into the Module window. 
U6lTp5vLk0R2dTfkUG6vXLGkjf1L0L_Gd-kFs3EH 
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. 
di3ZPjGD_2ijHBA9GJxtQFs56tnwkkE25tUe5K3R 
Run a backlink report on the client in question. Type your client URL in the Site Explorer. 
_cFNRbd2SiSXDZbr8VK1DbmoHM_O__ht07lm20L_ 
Click “Referring domains” in the menu on the left. 
Q-YW2gZPgEIdXd0ySVgyI5v73F_ru3XARS8D92ex 
Click “Export” on the right side. A new window will popup, select the “Microsoft Excel” radio button and click “Start Export”. 
Kiu7vXj6tHobdETbgdb7g9ZM578p0g1GBjiTUJlX 
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. 
9hnCbfjIbwFmMnh1mkJa5UnHSMbuhlMMS2xFTa8H 
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. 
wyXZwbCHBpIOdFm1T1qNhoyMddCr7l3HP_iJng0C 
Hold down ALT+F8 to open up the Macro window. Select PERSONAL.XLSB!HighlightDuplicateLinks and click “Run”. 
VBlZtS6pKLs3w4HI8BfGPv52crwGL31NVli-MeGW 
A new window titled “Look for Duplicates” will open. Click OK. 
JQR3MTMH2RzZ2LZTlmKnCxSzdqzJArACyZPc9wRv 
Once script is completed, (a few seconds depending on the amount of URL’s), click OK. 
oyunSGZa4EMmY3mWu3bJZvWNyHITzc718gpNVa_D 
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.  
-U2yGpQNSFbBMozRi1vsaHQu10uoB6I9pSf0E6QR 
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").EntireRow.Insert
                      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
                      
                      Next
                      
                         AutoFilterMode = False
                         Range("A1").AutoFilter
                         
                      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
                         .AutoFilter.Sort.Apply
                      
                         .Columns("A:A").ColumnWidth = 50 'for aesthetics
                         .Range("A1").Select
                         
                      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.
1
5,157 Views

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.