Excel 2010 – highlight cells used in any formula

microsoft-excel-2010

I will need to sum long column of values dividing them to some groups. And I do not want to miss some row. So I need to highlight all cells that I have already used in some formula (e.g.=D1+D2).

When I double click the formula, used cells are highlighted. I want this effect. I found that CTRL [ shall do this trick but it has no effect on my computer with Excel 2010. Is there some other possibility? Thanks

E.g. I have two columns, the first are data, the second is grouping. A4 is not used, A1..A3 shall be highlighted as already used.

**1**    =A1+A2
**3**    =A3
**2**
4

Best Answer

I don't think the layout is what you want, but this may suffice: You may be able to use Trace Dependents/Precedents

It lives in the Formula's toolbar, under Formula Auditing (and within the same place is the option to remove the arrows)

enter image description here

More details

However, I don't like the above. Now, it really depends on your needs, but I quickly wrote this VBa for you

Sub Button5_Click()

'PLEASE EDIT THIS FIRST BIT. 

Dim row As Integer
row = 1    ' THE STARTING ROW IN YOUR EXCEL SHEET

Dim numberOfRows As Integer
numberOfRows = 5    'THE TOTAL NUMBER OF ROWS YOUR WORKSHEET HAS

Dim columnWithFormula As String
columnWithFormula = "E"    ' THE COLUMN WHERE THE FORMULAs ARE (I suspect you are using B if your example is accurate))

Dim colourIndex As Integer
colourIndex = 26     ' WHAT COLOUR TO HIGHLIGHT COLUMNS. GOOGLE VBa COLOR INDEX

'AND STOP EDITING :)

For row = 1 To numberOfRows

If range(columnWithFormula & row).Value <> "" Then

   Dim result As String

   result = range(columnWithFormula & row).Formula

   result = Replace(result, "(", " ")
   result = Replace(result, ")", " ")
   result = Replace(result, "-", " ")
   result = Replace(result, "+", " ")
   result = Replace(result, "*", " ")
   result = Replace(result, "/", " ")
   result = Replace(result, "=", " ")
   result = Replace(result, ",", " ")

   Dim cells() As String
   cells = Split(Trim(result), " ")

   For j = 0 To UBound(cells)
    range(cells(j)).Interior.ColorIndex = colourIndex
   Next j

End If

Next row


End Sub

The above produces the following (I enabled formula so you can see what cells did have a formula in)

The macro isn't perfect, but I don't know your needs really so if you (as per the example in your post) have simple formula it should work fine.

Please note, it will change the cell background colour and undo won't work! So, if you are using highlighting already then you'll need to update the code! It also means after you run this macro, you'll need to manually select the work sheet and revert the background colour to your choice...

enter image description here

To expand this, you could update the for loop and replace it with 

For Each Cell in ActiveSheet.UsedRange.Cells
      'logic
Next

to check every single cell in the worksheet to see if there is a formula any where...

Related Question