Macro wont work with a cell which is updateing by formular

[m.paterson1]

I'm still not getting this to work by using the above, just run time error. I have also tried playing around with the below similar code, but once again it only works with manually updating the number and not by the formula updating the number.
If I cant get the below to work it there a Macro to copy the cell info from column AD and past as values in AC when a change is detected?

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim newVal As Variant
    ' Code won't work if multiple cells have been changed
    If Target.Count > 1 Then
        Exit Sub
    End If
    ' Only act if cell in column AC has been changed
    If Intersect(Range("AD:AD"), Target) Is Nothing Then
        Exit Sub
    End If
    ' Turn off events and screen updating
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    ' Get new value
    newVal = Target.Value
    ' Undo to restore old value
    Application.Undo
    ' Copy old value to adjacent cell
    Target.Offset(0, 1).Value = Target.Value
    ' Redo the change
    Target.Value = newVal
    ' Turn on screen updating and events
    Application.ScreenUpdating = True
    Application.EnableEvents = True
End Sub