PDA

View Full Version : [SOLVED] How do I lock the contents of a moving cell?



MikeM
07-07-2016, 07:35 AM
Private Sub Worksheet_Change(ByVal Target As Range)
If Range("m97").Value <> 0 Then
Me.Tab.ColorIndex = 3
Else
Me.Tab.ColorIndex = xlColorIndexNone
End If
End Sub

Ecel 16 - Windows 10 - PC

I am using the above code to change he colour of the tab if money is still to be paid on a project. However, the user needs, on occasion, to insert rows above the designated cell "m97". Is there a way for me to lock the contents wherever they end up? Much as Excel does when you insert rows above a "sum" formula. If you can help I appreciate your time. MikeM

snb
07-07-2016, 07:46 AM
Private Sub Worksheet_Change(ByVal Target As Range)
Me.Tab.ColorIndex = 3*abs(cells(rows.count,13).end(xlup)<>"")
End Sub

MikeM
07-07-2016, 08:13 AM
Sorry mark007 I should have headed the question with "Complete Muppet". I really don't understand your answer. Where do I insert the code and do I need the original reference cell? Sorry again this must be pretty boring for you guys. Thanks MikeM