Worksheet_Change

[truerip]

I know that I'm missing something simple and am hoping that someone can point out what I'm missing

Here goes:
I have an 8 tab spreadsheet (Excel 2013)
Formulas in Sheet1 151:A157 update with DAY()+1 based on the date entered into Sheet1 A1.
So with 9/8/18 entered into A1, A151=9; A152=10; A153=11 and so on.
I want my code to rename Sheet2 thru Sheet8 based on the contents of A151:A157 when I change the date in A1.
Example Sheet1 A1 = 9/8/18
Sheet1 A151 = 9 then rename Sheet2 to 9
Sheet1 A152 = 10 then rename Sheet3 to 10
Sheet1 A153 = 11 then rename Sheet4 to 11
and so on.

The code below works if I enter a value into A151:A157 directly, but not automatically from formulas when they change based off of new date entered into A1.

Any help would be greatly appreciated. Thanks!

Private Sub Worksheet_Change(ByVal Target As Range)


Dim Cells As Range


On Error Resume Next



For Each cell In Target
    If cell.Address = "$A$151" Then
        Sheets(2).Name = cell.Text
    ElseIf cell.Address = "$A$152" Then
        Sheets(3).Name = cell.Text
    ElseIf cell.Address = "$A$153" Then
        Sheets(4).Name = cell.Text
    ElseIf cell.Address = "$A$154" Then
        Sheets(5).Name = cell.Text
    ElseIf cell.Address = "$A$155" Then
        Sheets(6).Name = cell.Text
    ElseIf cell.Address = "$A$156" Then
        Sheets(7).Name = cell.Text
    ElseIf cell.Address = "$A$157" Then
        Sheets(8).Name = cell.Text
       
        
    End If
  
     
Next cell

If Err > 0 Then MsgBox "Sheet named renamed due to name conflict. Please try again."
End Sub

 

[RoryA]

Formulas don’t trigger a Change event. You should monitor the input cell A1 and then just loop through your range.

 

[truerip]

How would I add/modify code to monitor A1 so it would update automatically?

 

[RoryA]

Something like this:

Private Sub Worksheet_Change(ByVal Target As Range)


Dim n as long
On Error Resume Next


If not intersect(Target, Range("A1")) Is Nothing then
For n = 1 to 7
    Sheets(n + 1).Name = Range("A150").Offset(n).Text
next n
end if
If Err > 0 Then MsgBox "Sheet named renamed due to name conflict. Please try again."
End Sub

 

[My Aswer Is This]

I was working on this:
Script runs when A1 Changes

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  9/25/2018  4:25:17 PM  EDT
If Not Intersect(Target, Range("A1")) Is Nothing Then
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
On Error GoTo M
Dim i As Long
Dim x As Long
Dim ans As Long
    x = 151
ans = Month(Target.Value)
For i = 2 To 8
Sheets(i).Name = Cells(x, 1).Value: x = x + 1
Next
End If
Exit Sub
M:
MsgBox "That sheet name may already exist or  is a improper name"
End Sub