You may have inherited an Excel workbook that had worksheets with a combination of formula and hard-coded cells. You want to be able to quickly identify the cells with formula in them without having to step into each cell, or toggle formula view using Ctrl+~. The VBA code below will let you accomplish this in seconds.
Set up a new macro by inserting a new module in your workbook in the Visual Basic Editor (you access the Visual Basic Editor from the Developer tab, click here to find out how to activate your Developer tab in Microsoft Excel). In the new module window that opens up, enter the lines of code below:
Sub Formatcellswithformula()Dim i As Long
Dim j As LongFor i = 1 To 160
For j = 1 To 68
If Cells(i, j).HasFormula = True Then
Cells(i, j).Style = "Calculation"
End If
Next j
Next i
End Sub
The code above changes the cell format of the cells containing formulas to the preset Cell Style for “Calculation” in Microsoft Excel, as seen below. You can change “Calculation” to any other cell style of your preference.
Remember to change the end range of i and j (160 and 68) on lines 4 and 5 in the code above to the numbers of the last filled row and column in your worksheet.
One thought on “Excel Hack 22: Identify all cells in a range with a formula in Microsoft Excel”