VBA

chowell97 

Jan's technique works if the user changes only one cell at a time. But if the user changes multiple cells at once (e.g., by dragging the contents of a cell down in the Replaced column), that technique processes only the top of the changed rows (specifically, it uses only the first cell within Target).

It's also true that your existing/modified code recalculates the New value when columns in your table other than Current or Replaced are modified (or even if a cell outside the table is modified, if it's in a row shared by a table data row).

Consider this code instead:

Private Sub Worksheet_Change(ByVal Target As Range)

  Application.EnableEvents = False
    Dim tbl As ListObject
    Dim rngChgdCurrentCells     As Range
    Dim rngChgdReplacedCells    As Range
    Dim rngChgdRelevantCells    As Range
    Dim rngCell As Range
    Dim in4Row  As Long
    Dim N As Range  'the "New" cell for a row
    Dim C As Range  'the "Current" cell for a row
    Dim R As Range  'the "Replaced" cell for a row
    Dim strUserResponse As String
    
    '----
    On Error GoTo WkshtChg_ErrHndlr
    
    '----   In "the table" [description?], calculate/recalculate the
    '       New value in rows where either the Current value or the
    '       Replaced value changed.
    '  --   Determine if any of the changed cells are relevant to
    '       the calculation of a New value.
    Set tbl = ActiveSheet.ListObjects(1)
    Set rngChgdCurrentCells = Intersect(Target, _
            tbl.ListColumns("Current").DataBodyRange)
    Set rngChgdReplacedCells = Intersect(Target, _
            tbl.ListColumns("Replaced").DataBodyRange)
    If rngChgdCurrentCells Is Nothing _
    And rngChgdReplacedCells Is Nothing Then
        '...the changed cell(s) is/are not relevant.
        GoTo WkshtChg_Exit
    End If
    '  --   Load references to all relevant changed cells into a
    '       single variable.
    If rngChgdCurrentCells Is Nothing Then
        Set rngChgdRelevantCells = rngChgdReplacedCells
    ElseIf rngChgdReplacedCells Is Nothing Then
        Set rngChgdRelevantCells = rngChgdCurrentCells
    Else
        '...(it's unlikely, but) both types of cells were changed.
        Set rngChgdRelevantCells = Union( _
                rngChgdCurrentCells, rngChgdReplacedCells _
            )
    End If
    '  --   Examining each changed relevant cell, calculate a New value
    '       in the same row.
    For Each rngCell In rngChgdRelevantCells
        in4Row = rngCell.Row
        Set C = Intersect(rngCell.EntireRow, tbl.ListColumns("Current").DataBodyRange)
        Set R = Intersect(rngCell.EntireRow, tbl.ListColumns("Replaced").DataBodyRange)
        Set N = Intersect(rngCell.EntireRow, tbl.ListColumns("New").DataBodyRange)
        '
        If IsEmpty(R.Value) Then
            '...Replaced value has not been entered.  Do nothing. [?]
        ElseIf R.Value = "No" Then
        '   [...use that for a case-sensitive test; if case does not matter, use:
        'ElseIf StrComp(R.Value, "No", vbTextCompare) = 0 Then
            N.Value = C.Value
        Else
            'Because "Entry is required", prompt until a non-empty response is
            'received.
            Do
                strUserResponse = InputBox("Please enter City, State, and ZIP of" _
                        & " site address", "Entry is required")
            Loop Until Len(strUserResponse) > 0
            N.Value = strUserResponse
        End If
    Next rngCell
    
WkshtChg_Exit:
    Application.EnableEvents = True
    Exit Sub

WkshtChg_ErrHndlr:
    Dim in4ErrorCode    As Long
    Dim strErrorDescr   As String
    Dim strMessage      As String
    Dim in4UserResponse As VbMsgBoxResult
    '  --   Capture info about the processing error.
    in4ErrorCode = Err.Number
    strErrorDescr = Err.Description
    '  --   Notify the user.
    strMessage = "Error " & in4ErrorCode & " occurred while processing row " _
            & Format$(in4Row, "#,###,##0") & vbCrLf & strErrorDescr
    in4UserResponse = MsgBox(strMessage, vbRetryCancel)
    '  --
    If in4UserResponse = vbRetry Then
        Resume
    Else
        Resume WkshtChg_Exit
    End If
End Sub

I've included an example of a simple error handler.

Note that your test for "No" is case-sensitive. I included commented-out code for an alternative, case-insensitive test.

I also wrapped the prompt for site address in a Do loop, as the prompt title indicates "Entry is required". (But as written, the user could defeat this by just entering a space.)

And let me point out that it is generally poor design to put multiple types of values into one table column, as you have done putting both a date value and a site address into New. This may be completely functional, but it may also make future usage and/or modifications more difficult.