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)

  • Would require VBA. – chuff Jan 13 '13 at 10:27

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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