Assume your worksheet contains a large number of external links, and you now have to dynamically identify the cells that contain the links and references. In this tutorial, we will show you how to find external links in our worksheets and mark them with Conditional Formatting in Excel.
Find External Links in Conditional Formatting in Excel: 2 Effective Ways
In the image below, we have provided a data set containing an external link from another workbook ‘Previous Data’. Now, we will apply the Find & Select option to find the external links and then apply Conditional Formatting. Later on, we will run a VBA code to build a function to automatically marked by Conditional Formatting.
1. Apply the Find & Select Option to Find External Links and Results in Conditional Formatting in Excel
To apply the Find & Select option, follow the outlined procedures below.
Step 1:
- Go to the Home
- Click on the Find & Select drop-down list.
- Then, select the Find option.
Step 2:
- Type the file extension to find particular files. We type (.xl) to find the external excel files.
- Then, click on the Find All.
- Therefore, you will get all the external links that exist in your workbook.
- Notice that, you will get the cell references that contain the links. Now, we will apply Conditional Formatting.
Step 3:
- Firstly, click on the Conditional Formatting.
- Then, select the New Rule.
Step 4:
- Select the option Use a formula to determine which cells to format.
- In the Format values where this formula is true box, type a cell with absolute reference that contains external links ($E$5).
- Then, click on the Format option to select a colour.
Step 5:
- Pick a colour and click on OK.
Step 6:
- Finally, press Enter on click OK.
- As a result, you will get the cell formatted with Conditional Formatting.
Notes. For any reason, if you want to delete all the external links, follow the steps below.
Step 1:
- Go to the Data.
- Click on the Edit Links.
Step 2:
- Then, click on the Break Link.
- Therefore, all the links associated with the worksheet will be removed.
2. Run a VBA Code to Find External Links in Conditional Formatting in Excel
By applying a VBA code, we can automate the process. Follow the instructions below to do so.
Step 1:
- Press Alt + F11 to open the VBA Macro.
- From the Insert tab, select the Module.
Step 2:
- Paste the following VBA code.
'Give your function a name (ExtraLink)
Function ExtraLink(Linked_Cell As Range) As Boolean
'Apply the If condition
If Linked_Cell.HasFormula Then
ExtraLink = VBA.InStr(1, Linked_Cell.Formula, "[") > 0
End If
End Function
Sub Find_Link()
End Function
Step 3:
- Select all the cells in the range B5:E11.
Step 4:
- Then, click on the Conditional Formatting.
- Select the New Rule.
Step 5:
- At first, click on the Use a formula to determine which cells to format.
- In the box, type the newly created function extralink.
- Type the first cell reference (B5) of your range inside the extralink.
- Finally, click on the Format to add colour.
Step 6:
- Pick a colour and click OK.
Step 7:
- Finally, press Enter or click OK.
- Consequently, all the cells containing external links are highlighted with the desired color.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
Conclusion
Finally, I hope you now know how to find external links in conditional formatting in Excel. All of these tactics should be performed while your data is being educated and practiced. Look over the practice book and put what you’ve learned to use. Because of your generous support, we are motivated to continue delivering initiatives like these. Please do not hesitate to contact us if you have any questions. Please let us know what you think in the comments area below.
Related Articles
- How to Apply Different Types of Conditional Formatting in Excel
- How to Apply Conditional Formatting for Blank Cells in Excel
- How to Apply Conditional Formatting on Multiple Columns in Excel
- How to Compare Two Columns Using Conditional Formatting in Excel
- How to Remove Conditional Formatting in Excel
- How to Remove Conditional Formatting but Keep the Format in Excel
- How to Do Conditional Formatting in Excel
- Conditional Formatting with Formula in Excel
<< Go Back to Conditional Formatting | Learn Excel