Option Explicit
Private Sub Worksheet_Activate()
Call Cells(8, 4).Select
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Address = "$D$8" Then _
If .Value <> vbNullString Then _
Call prcResetDate(probjTarget:=Target)
End With
End Sub
Private Sub prcResetDate(ByRef probjTarget As Range)
Dim objCell As Range
Set objCell = Tabelle2.Columns(2).Find(What:=probjTarget.Value, _
LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
If Not objCell Is Nothing Then
With objCell
If MsgBox("Der Wert " & .Value & " wurde gefunden," & _
" möchten Sie das Datum in Spalte D ersetzen?", _
vbExclamation + vbYesNo, "Wert gefunden!") = vbYes Then _
.Offset(0, 2).Value = Date
End With
Call Tabelle1.Cells(8, 4).ClearContents
Set objCell = Nothing
End If
End Sub