How to highlight cells with external links in Excel?

Referring to the source workbook is the quickest approach to pull data from one file into another file when you wish to pull data from another file. Excel users are accustomed to routinely utilizing such external links, often known as external references. Excel will automatically generate a connection to the other workbook if you use a reference to a data point that is located in another workbook while formulating in Excel. Finding references or external links in a spreadsheet manually is a laborious operation that should be avoided if possible. Even though Microsoft does not have a built-in function that can locate external references or links, there are still several workarounds that can be used to accomplish this goal. Suppose that your worksheet contains a significant number of connections to external websites; now suppose that you need to automatically highlight the cells in your workbook that contain those links.

If you want to highlight cells in Excel that include external links, you will need to first build a User Defined Function and then use the Conditional Formatting function. Excel does not provide a straightforward method for doing this. In this tutorial we are going to learn about highlighting cell with external links using VBA.

Highlight cells with external links using conditional formatting

Step 1

Open an excel sheet and Press Alt and F11 key (Alt+F11) to open Microsoft Visual Basic for Application windows. See the below given image.

Step 2

After that, select Insert > Module from the menu bar to bring up the popup Module window. See the below given image.

Step 3

After opening the Module Window, then type the following VBA code in it.

Function ExternalFormula(pCell As Range) As Boolean
   If pCell.HasFormula Then
      ExternalFormula = VBA.InStr(1, pCell.Formula, "[") > 0
   End If
End Function

After that, select the range or the entire worksheet that you want to highlight the external links on, save the changes, and then return to the previous worksheet by closing the window.

Step 4

then go to Home > Conditional Formatting > New Rule.

See the image given below.

Step 5

In the box saying "New Formatting Rule," click Use a formula to determine which cells to format. Then in the Format values where this formula is true text box, type the following formula.

 =externalformula(A1) 

In the above formula, A1 is the first cell of the selected cells.

See the below image given.

Step 6

Then, click the Format button to go to the Format Cells dialogue. Under the Fill tab, choose a color as per your need. See the below given image.

Then Click OK>OK.

Now you can see If you put other formulas or external links in the selection that Conditional Formatting includes, they will automatically be highlighted.

See the image given below.

Conclusion

In this tutorial, you learnt about how to highlight cells with external links using VBA code and conditional formatting.