Md. Araf Bin Jayed
In Microsoft Excel, for analysis and calculation, we use different types of formulas. As a default feature in Excel, after clicking on the cells containing some formula, other cells related to that formula get highlighted. This feature is really helpful for users, as it clearly demonstrates the function and use of that formula in determining value. But in some cases, cells don’t get highlighted after clicking on the formula. For that reason, we will show you the solution to the problem of cells are not highlighting in the Excel formula in this article.
Download Practice Workbook
You can download the free Excel workbook here and practice on your own.
2 Suitable Solutions If Cells Are Not Highlighting in Excel Formula
In this article, you will see two suitable solutions for the problem of cells are not highlighting in the Excel formula. For our first procedure, we will turn on Allow editing directly in cells from the Excel Options. Secondly, we will take the help of VBA code to solve the problem.
For both procedures, we will use the following data set. Here, we have the total sales and cost of a product in rows 6 and 9 respectively.
1. Turning On Allow Editing Directly in Cells Option
Some features or options in Excel may be turned on or off, causing users various problems while working. One such option is Allow Editing Directly in Cells. You can solve this problem of cells are not highlighting in formulas by turning on this option. The detailed steps of this procedure are as follows.
Step 1:
- First of all, calculate the profit in the data set by subtracting the total cost from the total sales.
- This will create a formula in cell C10.
Step 2:
- Secondly, after double-clicking on cell C10, you will see that cells C6 and C9 will not be highlighted.
- So, we will solve this issue in the following steps.
Step 3:
- Thirdly, go to the File tab of the ribbon.
Step 4:
- Fourthly, from the Excel Home window, select Options.
Step 5:
- Fifthly, you will see the Excel Options dialog box.
- Then, go to the Advanced tab in the box.
- From there, mark the option Allow editing directly in cells.
- Lastly, press OK.
Step 6:
- Finally, after enabling the above option, the cells related to the specific formula will be highlighted after double-clicking on the formula cell.
Read More: How to Highlight Cell If Value Is Less Than Another Cell in Excel
2. Applying VBA Code
In our second procedure, we will apply a VBA code to solve our problem. Here, by inserting the proper command and sequence, we can solve this problem automatically. See the following steps for a better understanding.
Step 1:
- First of all, take an Excel sheet with the data set containing the following problem in which formula cells are not highlighting
Step 2:
- Secondly, to solve this problem, go to the Developer tab from the ribbon.
- Then, choose Visual Basic from the Code group.
Step 3:
- Thirdly, you will see the VBA window.
- Then, from the Insert tab, choose Module.
Step 4:
- Fourthly, copy the following code and paste it into the module.
'Addressing the function name
Sub Highlighting_Cells_in_Excel_Formula()
'Turning on editing directly in cell thorugh the command
Application.EditDirectlyInCell = True
End Sub
VBA Breakdown
- Firstly, we are calling the Sub procedure Highlighting_Cells_in_Excel_Formula.
Sub Highlighting_Cells_in_Excel_Formula()- Secondly, the following command will turn on the options of cell editing.
Application.EditDirectlyInCell = True
End SubStep 5:
- Fifthly, save the code in the module.
- Then press F5 or the run button to run it.
Step 6:
- Finally, after running the code, double-click on cell C10.
- Here, you will see the not highlighting problem will get solution, and the cells related to the formula will be highlighting.
Notes:
- If there are multiple workbooks that have been opened, then close the other workbooks that you are not working with. Because only the focused workbook’s formula cells will be highlighted only, after double-clicking.
- Sometimes, you can save your current progress and then close the workbook. After reopening the workbook, you may not find this problem again.
Read More: How to Highlight Blank Cells in Excel VBA (6 Suitable Examples)
Conclusion
That’s the end of this article. We hope you find this article helpful. After reading the above description, you will be able to solve the problem if cells are not highlighting in the Excel formula error by using any of the above-mentioned solutions. Please share any further queries or recommendations with us in the comments section below.
The ExcelDemy team is always concerned about your preferences. Therefore, after commenting, please give us some moments to solve your issues, and we will reply to your queries with the best possible solutions ever.
