Automatically reflect List changes in data validation cells

I was able to use the VBA code (see below) posted by JBeaucaire to automatically update changes to cells referencing a list when the list was modified.

The code worked perfectly. The issue is reopening the spreadsheet and adding new cells that refer to my list. The code no longer automatically runs the macro to index the cell. I have tried deleting the code and adding it back but other than manually typing/copying the formula in the cell it isn't working anymore.

I am new to VBA in excel so I am not sure if this is expected behavior or if there is something else I need to do.

Thanks in advance for your help

-----------------------------------------

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
'Summary: Make choices from DV drop downs into formulas, so any changes
' in the source lists will flow out to the already filled in cells
Dim strValidationList As String
Dim strVal As String
Dim lngNum As Long

On Error GoTo Nevermind
strValidationList = Mid(Target.Validation.Formula1, 2)
strVal = Target.Value
lngNum = Application.WorksheetFunction.Match(strVal, Range(strValidationList), 0)

If strVal <> "" And lngNum > 0 Then
Application.EnableEvents = False
Target.Formula = "=INDEX(" & strValidationList & ", " & lngNum & ")"
End If

Nevermind:
Application.EnableEvents = True

End Sub