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
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
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!