Add a new named range each time a cell changes

[Leonvl]

When a cell in the range A1:A100 is changed (added), I want to define a named range that referring to the two cells to the right. The name of the range can be the value of the target cell. I tried the below code but it doesnt work for some reason...

Private Sub Worksheet_Change(ByVal Target As Range)

Dim NamedRange As Range
Dim RangeName As String


If Not Intersect(Target, Range("A1:A100")) Is Nothing Then
    Set NamedRange = Worksheets("Blad1").Range(Target.Offset(, 1), Target.Offset(, 2))
    RangeName = Target.Value
    Target.Names.Add Name:=RangeName, RefersTo:=NamedRange
End If
End Sub

 

[My Aswer Is This]

I would have done it this way.
But if you want to stick with your way that's fine with me.

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified 6/17/18 3:10 AM EDT
If Not Intersect(Target, Range("A1:A100")) Is Nothing Then
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
ans = Replace(Target.Value, " ", "")
Target.Offset(, 1).Resize(, 2).Name = ans
End If
End Sub