Range Name Based off of Cell Value

[scherich]

I want to have a range name based off of a cell value. So, for example:

If A1:A2 is the range I want named, what would I do so that the name of that range is based off of the text in B1?


I don't know if this is possible, but I appreciate any help!

[Alf]

It is possible if you use a macro. Something like this perhaps.

Option Explicit
Sub CreateName()
Range("A1:A2").Name = Range("B2")
End Sub

Problem with that is that the name is static i.e. if you change the value in B2 the new name will not get updated unless you rerun the macro. The workaround is to use the "Worksheet_Change" event. Then name will then get updated every time value in B2 is changed.

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Application.Intersect(Target, Range("B2")) Is Nothing Then
    Range("A1:A2").Name = Range("B2")
    Else
    Exit Sub
    End If
  
End Sub

To add this code right click on the active sheet, click code and paste this code in the windows that opends. This code is only active on the sheet it is pasted to.

Alf