Excel, statement in cell doesn't disappear like expected

Question

When I enter a value in Sheet 2 D1, the number should be entered in the table like if-statement and after that, the statement should disappear and the value should be still entered in the cell.

Table:

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim OperationalArea As Range, AffectedArea As Range
    Set OperationalArea = Me.Range("B2:G7")
    Set AffectedArea = Intersect(Target, OperationalArea)
    
    If Not AffectedArea Is Nothing Then
        Application.EnableEvents = False
        AffectedArea.Value = AffectedArea.Value
        Application.EnableEvents = True
    End If
End Sub

Answer 1

The event WorkSheet_Change() will not trigger if the change is caused only by recalculation.

You can use an event in Sheet2 instead:

' Event in Sheet2
Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Not Application.Intersect(Target, Me.Range("D1")) Is Nothing Then
    
        ' Change "Sheet1" to the name of the sheet with the table in B2:G7
         ThisWorkbook.Worksheets("Sheet1").Range("B2:G7") = Me.Range("D1")
    
    End If
    
End Sub