This has to do with the nature of worksheet protection. The purpose of locking cells is to prevent users from changing the contents. If users delete rows, they will be permanently changing the cells, so it makes sense that Excel doesn't allow users to delete rows with locked cells. What's confusing is that Excel gives you the option to select "delete rows" in the worksheet protection dialog, even though that setting doesn't seem to do anything. The only complete solution is to use macros to unprotect the sheet, delete/copy the rows, and protect it again. If you want the cells to be locked for most users, but allow some users to delete rows, another option is to use the "allow users to edit ranges" feature to allow particular users or users with a password to modify cells even though they are locked. See How to Sort Locked Cells in Protected Worksheets That article is about sorting, but it explains the relevant concepts well.
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)
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...
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...
Best Answer
Select the rows you want to nest together and then, in the Data ribbon, click Group.
A new column appears to the left of the row headers. Clicking the
-collapses the rows. Clicking the+expands the rows.