How to highlight all cells referenced by a formula in Excel?
This article is talking about highlighting all cells which was referenced by a formula in Excel. Please follow the below two methods to get it done.
Highlight all cells referenced by a formula with Shortcut key
Highlight all cells referenced by a formula with VBA code
Highlight all cells referenced by a formula with Shortcut key
This section will recommend you to use Shortcut key to select all cells which was referenced by a formula, and then manually fill color to them.
1. Supposing E1 is a formula cell, and you need to highlight all cells which was referenced by it. Please select the formula cell E1, then press Ctrl + [ (the open-square-bracket) keys simultaneously.
2. Now cells that referenced by this formula are selected, please specify a fill color to highlight them. See screenshot:
Highlight all cells referenced by a formula with VBA code
You can also running VBA code to highlight all cells which was referenced by a formula in Excel.
1. Press Alt + F11 keys to open the Microsoft Visual Basic for Applications window.
2. In the Microsoft Visual Basic for Applications window, please click Insert > Module. Then copy and paste the below VBA code into the Module window.
VBA code: Highlight all cells referenced by a formula in Excel
Sub HighlightCellsReferenced()
Dim rowCnt As Integer
Dim i As Integer, j As Integer, strleng As Integer
Dim strTxt As String, strFml As String
Dim columnStr, cellsAddress As String
Dim xRg As Range, yRg As Range
On Error Resume Next
Set xRg = Application.InputBox(Prompt:="Please select formula cell(s)...", _
Title:="Kutools For Excel", Type:=8)
strTxt = ""
Application.ScreenUpdating = False
For Each yRg In xRg
If yRg.Value <> "" Then
strFml = yRg.Formula + " "
strFml = Replace(strFml, "(", " ")
strFml = Replace(strFml, ")", " ")
strFml = Replace(strFml, "-", " ")
strFml = Replace(strFml, "+", " ")
strFml = Replace(strFml, "*", " ")
strFml = Replace(strFml, "/", " ")
strFml = Replace(strFml, "=", " ")
strFml = Replace(strFml, ",", " ")
strFml = Replace(strFml, ":", " ")
For j = 1 To Len(strFml)
If Mid(strFml, j, 1) <> " " Then
cellsAddress = cellsAddress + Mid(strFml, j, 1)
Else
On Error Resume Next
Range(cellsAddress).Interior.ColorIndex = 3
cellsAddress = ""
End If
Next
End If
Next yRg
Application.ScreenUpdating = True
End Sub
3. Press the F5 key to run the code. In the popping up Kutools for Excel dialog box, please select a formula cell or multiple formula cells which you need to highlight referenced cells based on, and then click the OK button. See screenshot:
Now all referenced cells based on the selected formula cell(s) are highlighted in red immediately as below screenshot shown.
Related articles:
Best Office Productivity Tools
Supports Office/Excel 2007-2021 and 365 | Available in 44 Languages | Easy to Uninstall Completely
Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need Is Just A Click Away...
Supercharge Your Excel Skills: Experience Efficiency Like Never Before with Kutools for Excel (Full-Featured 30-Day Free Trial)
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day! (Full-Featured 30-Day Free Trial)





