In Microsoft Excel, we use different formulas for analysis and calculation. 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 not highlighting in the Excel formula in this article.
If Cells Are Not Highlighting in Excel Formula: 2 Suitable Solutions
In this article, you will see two suitable solutions for the problem: 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.
- 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.
- Next, 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.
- Go to the File tab of the ribbon.
- From the Excel Home window, select Options.
- 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.
- 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 Selected Cells 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.
- First of all, take an Excel sheet with the data set containing the following problem in which formula cells are not highlighting
- Next, to solve this problem, go to the Developer tab from the ribbon. If you cannot find the Developer tab in the ribbon, click here to display the developer tab on the ribbon.
- Then, choose Visual Basic from the Code group.
- After that, you will see the VBA window.
- Then, from the Insert tab, choose Module.
- Next, copy the following code and paste it into the module.
'Addressing the function name
'Turning on editing directly in cell thorugh the command
Application.EditDirectlyInCell = True
- Firstly, we are calling the Sub procedure Highlighting_Cells_in_Excel_Formula.
- Secondly, the following command will turn on the options of cell editing.
Application.EditDirectlyInCell = True
- Save the code in the module.
- Then press F5 or the run button to run it.
- Finally, after running the code, double-click on cell C10.
- Here, you will see the not highlighting problem will get the solution, and the cells related to the formula will be highlighting.
- 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.
Download Practice Workbook
You can download the free Excel workbook here and practice on your own.
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.
- How to Highlight Blank Cells with Conditional Formatting in Excel
- How to Highlight Cells in Excel but Not Print
- How to Highlight Cell If Value Is Less Than Another Cell in Excel
- How to Highlight Cells in Excel Based on Value
- How to Click One Cell and Highlight Another in Excel
- How to Highlight Cells Based on Text in Excel
- Highlight Cells That Contain Text from a List in Excel
- How to Highlight Cell Using the If Statement in Excel