Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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
share|improve this question
1  
It isn't clear, but it sounds like you are trying to highlight (at one time) ALL cells used in ALL formulas. Is that correct? –  CharlieRB May 14 at 11:50
    
Yes, I needed to easily identify unused cells in that column. Meanwhile I just compared sum of all rows and sum of all groupings to verify that they matches. But it would be useful to know the trick for future. –  Leos Literak May 14 at 11:54

2 Answers 2

up vote 3 down vote accepted

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...

share|improve this answer
    
Thanks, it does the job though I have to click this function for each formula manually. You should see dense net of arrows :-) –  Leos Literak May 14 at 12:12
    
@LeosLiterak, I updated it so it is (may) be more automated for you. –  Dave May 14 at 12:30
    
Thanks. I just tried and I failed. 1) typo: Dim reslut -> result. 2) How to use this script? I opened VB from Development bar. Then I chose View code on my sheet and copied Sub Button5_Click() code there updating mentioned variables. 3) what is the next step? –  Leos Literak May 20 at 8:08
    
@LeosLiterak, sorry, there was a typo, I had to a lot of the code from memory.. Can you try now, I updated the script (So recopy the code above) –  Dave May 20 at 8:13
    
This was different typo than I meant :-) Dim reslut As String –  Leos Literak May 20 at 8:21

go to a blank cell - in this cell type =V40=1:1048576 where the first reference (in this case v40) refers to another blank cell. The second part highlights the entire sheet.

Press enter and then click on the cell, click on view formulas and double click trace precedents. This will draw a blue link to every cell that is referred to in another cell. Look for the ones with no lines - they have been missed.

Ash

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.