I have cells "C9:C100" all of them have a value linked from a different
cell on a different Sheet, within the same Workbook.

EX:

C9 in worksheet A is linked to D12 in worksheet B. If there is any
changes done to D12 in worksheet B it shows in realtime on C9 in
worksheet A, but I want a time stamp to show the change on D9 in
worksheet A.

I found the following Macro and it works great, but it will not update
the timestamp when the link is changed. It just keeps the original
timestamp.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count 1 Then Exit Sub
If Not Intersect(Range("D2:D3000"), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, 1).ClearContents
Else
With .Offset(0, 1)
.NumberFormat = "mmm dd yyyy hh:mm:ss"
.Value = Now
End With
End If
Application.EnableEvents = True
End If
End With
End Sub