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 have a excel sheet with I use as a sort of expense log

It is structured as follows:

A1 to A4 -> Some numeric values

A5-> =SUM(A1:A4)

A6 to A10 ->Some numeric values

A11 -> SUM(A5:A10)

So, I essentially keep a sort of checkpoint after every few rows

Now, I have 200+ rows and want to check if I missed out on any of the rows when applying the formulae

Is there an easy way to just select the entire range and tell excel to highlight the cells which are not referenced by a formula?

(trace precedents is a way, but it doesnt make it easy to read and some of the cell depend on numbers outside this range so it goes branching off there as well)

share|improve this question
    
Would require VBA. –  chuff Jan 13 '13 at 10:27
add comment

1 Answer

I'm not sure if I understand your question 100%.
But this macro will highlight all cells who are using a formula

  • Open a copy of your excel sheet containing all the data
  • press ALT+F11 to open the VBA editor
  • open sheet1 from the left pane and insert the code below

    Sub highlight()
      For Each mycell In UsedRange
        If mycell.HasFormula Then mycell.Interior.ColorIndex = 6
      Next mycell
    End Sub

  • go back to your sheet and press ALT+F8 and execute your macro

share|improve this answer
add comment

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.