Cell change, reset formula?

[Kathrine]

(also posted in Programming)

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...?).

 

[Gary''s Student]

You are very welcome. Post whenever you hit a roadblock

 

[Gary''s Student]

The problem is that you are applying the formula to target:
Target.Formula="=C" & rr & " - " & "D" & rr
How about restricting it to:
Intersect(Target, Range("Protectarea")) .Formula="=C" & rr & " - " & "D" & rr

 

[Kathrine]

You're so right. This is exactly what I want and need.. The problem is I
don't know anything about VBA programming, I'm actually a doctor - working
with humans - not computers. Still I'm trying to do a lot of "advanced stuff"
in Excel these days.. Guess I'm learning by doing.... I'm really glad the
discussions pages exits though, otherwise I would have been lost..

Thank you
Kathrine