Reset cell after change
 

Kathrine,

try it this way:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rr As Long
Dim rng As Range
Dim c As Range

Set rng = Intersect(Target, Range("Protectarea"))
If Not rng Is Nothing Then
For Each c In rng
rr = c.Row
Application.EnableEvents = False
c.Formula = "=C" & rr & " - " & "D" & rr
Application.EnableEvents = True
Next c
End If
End Sub



--
Hope that helps.

Vergel Adriano


"Kathrine" wrote:

I have a situation in Excel where I want to restore the formula in a cell in
case of damage. I want to do this because for various reasons I cannot use
standard sheet protection. Also, I'm not really familiar with VBA
programming so I'm really struggeling and getting very little sleep these
nights.....

If you have time, please take a look at my problem and give me some hints,
even if it's just to tell me it's not possible....

I've implemented the following code, restoring formula =C - D if target is
within my "protectarea" (e.g. "E:E"):

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rr As Long
If Not Intersect(Target, Range("Protectarea")) Is Nothing Then
rr = Target.Row
Application.EnableEvents = False
Target.Formula = "=C" & rr & " - " & "D" & rr
Application.EnableEvents = True
End If
End Sub

This works fine until I change multiple cells in a range that partly overlap
my "protect area". In such case all cells will of course be changed according
to formula, even though I just want to manipulate those in "protect area".

q1. Is there any way to solve this?

q2. If not, can I prevent the user to even select the cells in Protectarea?
This would actually be a better solution. Is it possible to achive this in
worksheet_selectionchange()? I'm thinking similar to sheetprotection with not
being able to select locked cells, only with sheetprotection switched off
(confused...?).