I don't understand what you are trying to cater for, doesn't make sense.
Explain with examples

Regards,
Peter T

"ML0940" wrote in message
...
Hi Peter
Than you again for taking the time
All of your assunmptions were indeed correct.
I think this is a clever way to handle it, except, I can not have a named
range be replaced with another name, as other macros use those named
ranges.

Is it possiblle to delete and create it again with the same name?
Perhaps you could do
currrange = "exisitng named range"
Then
newrange = currrange

Other then that, it sounds like a great idea

ML


"Peter T" wrote:

See how you get on with this -

' in the ThisWorkbook module
Private Sub Workbook_SheetChange(ByVal Sh As Object, _
ByVal Target As Range)
Dim b1 As Boolean, b2 As Boolean
Dim r1 As Range, r2 As Range

On Error Resume Next
Set r1 = Range("aaa")
Set r2 = Range("bbb")

On Error GoTo errExit

b1 = r1 Is Nothing And Not r2 Is Nothing

If Not b1 Then
b2 = r2 Is Nothing And Not r1 Is Nothing
End If

If b1 Or b2 Then
Application.EnableEvents = False
If b1 Then
r2.ClearContents
ElseIf b2 Then
r1.ClearContents
End If

Else

If Sh Is r1.Parent Then
b1 = Not Intersect(Target, r1) Is Nothing
End If

If Not b1 Then
If Sh Is r2.Parent Then
b2 = Not Intersect(Target, r2) Is Nothing
End If
End If

If b1 Or b2 Then
On Error GoTo errExit
Application.EnableEvents = False
If b1 Then
r2.Value = r1.Value
Else
r1.Value = r2.Value
End If
End If

End If

errExit:
If b1 Or b2 Then Application.EnableEvents = True
End Sub

As written it might not cater for all your needs (eg entire deletion of
rows/cols containing a named range) or not quite the way you want to (eg
ranges not same size).

If both ranges are on the same sheet it would be better to use the
equivalent event in the sheet module (don't just paste the above into a
sheet module, get the correct 'Change' event vs the above 'SheetChange',
also remove the "If Sh Is r1.Parent Then" checks.

Regards,
Peter T

"ML0940" wrote in message
...
Hi,
Here is a good one I have been pondering for a while, still, in all my
efforts, I have not gotten what I really need. I am not an Excel-VBA
expert,
so I am hoping somebody in here can give some insite.

OK, unforunately, there is no row insert/delete event..WISH LIST

I have 2 seperate named ranges; if I change one, I would like the
other to
change and look exactly the same as the one that I just changed.
That means, if I delete a row in one, then I want the other to delete
a
row
and update all the values in the cells to match precisely. Same if I
add a
row.

I hope this makes sense and is this possible?

If may work on a Change event but it is not necessary really; if I
change
one, then I don't mind clicking a macro to update.

Thanks in advance!

ML