Clear content in a column, based on specific cell value, using VBA

[Elena Margulis]

I have the following Table in my macro-enabled excel file
Table 1:

My goal is to clear cells in Column D only, if a cell value (in column D) = 999, before I close this workbook.
User can create a new record(s) in this Table and a new cell value in Column D could be = 999, so whenever any existing or new record has cell value = 999 in Column D - this value must be cleared.
Below, plz. see my expected result:
In a screenshot below, user entered a new records - 5 thru 8. record 7 in Column D had 999 value also - cleared content before closing workbook
and that's what we see after re-opening this workbook:

I tried the following code (and a few more), but keep getting errors.
Not sure - how do I have to define a Workbook and then - how to correctly select the Column D of this Table1
Could be other mistakes as well;

Using vba from time to time only (on rare occasions)
Plz HELP!

Here is one of the VBA examples I tried:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
  
   Dim sht As Worksheet
   Set sht = ThisWorkbook.ActiveSheet
 
    If Worksheets("Sheet1").ListObjects("Table1").Columns(4).Value = 999 Then
       Worksheets("Sheet1").ListObjects("Table1").Columns(4).ClearContents
      
    End If
    
 End Sub

 

[alansidman]

Try this air code. No file presented to test. Suggest instead of pictures, you use XL2BB.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim i As Long, lr As Long
    lr = Range("D" & Rows.Count).End(xlUp).Row
    For i = 2 To lr
        If Range("D" & i) = 999 Then
            Range("D" & i).ClearContents
        End If
    Next i

End Sub