Hello gentlemen

You are right I should have provided more details. This is borne out in that
the result I
was looking for is not what I expected. Let me explain with an example.

Let say columns A1 and IV1 are the cells locked.

Const refer_str1 = "=Sheet1!$A$1"
Const refer_str2 = "=Sheet1!$IV$1"

A1 has the formula =IV1
IV1 contains a value

Then delete column T (or any column)

IV1 still contains the same value
A1 formula now is =IU1

The question now is - how do you prevent A1 formula changing.

Pat



"Frank Kabel" wrote in message
...
Hi Tom
I had one advantage: I provided the original code to him :-)
But you're right, the OP should state his intentions with more detail
Regards
Frank


Tom Ogilvy wrote:
Frank made some assumptions on what you want to do - but unless he
nailed it, you would need to state what you mean by add a second
range. You set a defined name to your range, so adding a second
name would mean you want to then redefine that same named range to
the second range? If not state what you actually want to do. If you
want to reuse the logic of your code, then you need to move it to a
subroutine or function and pass it the data you want to work with.


Pat wrote in message
...
The following code was kindly provided to me:

Private Sub Worksheet_Change(ByVal Target As Range)
Const fixed_name = "lock_formula"
Const refer_str = "=Sheet1!$H$1:$H$800"
Dim rng As Range
Dim save_formula

If IsError(Evaluate(fixed_name)) Then
ActiveWorkbook.Names.Add Name:=CStr(fixed_name),
RefersTo:=CStr(refer_str)
Else
If CStr(ActiveWorkbook.Names(CStr(fixed_name)).Refers To) <
CStr(refer_str) Then
On Error GoTo CleanUp
Application.EnableEvents = False
Set rng = Evaluate(fixed_name)
save_formula = rng.Formula
rng.Value = ""
Set rng = Range(CStr(refer_str))
rng.Formula = save_formula
ActiveWorkbook.Names(CStr(fixed_name)).RefersTo =
CStr(refer_str)
Else
'do nothing
End If
End If

CleanUp:
Application.EnableEvents = True
End Sub


I now want to include another range within the code:

Const refer_str = "=Sheet1$R$1:$R$800"

can anyone help with this?

thanks
Pat