clear content of F9 and F10 cell if D9 has value1205

[SIPRIAN]

HI,

I'm trying to clear content of F9 and F10 cell if D9 has value1205.
I used the macro below however, it didn't work


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$D$9" Then
If Target.Value = UCase("1205") Then Sheets("Sheet2").Range("f9:F10 ").ClearContents ""
End If
End Sub


I have Microsoft 365

Thank you for your time

 

[SIPRIAN]

These are the other values
12304
12039
6598
1190
1190
11410
10096
1051
1115
10260
10002
10002
1066
10554
1066
9952
10209
9952
1066
9952
9952
10209
9952
1190
1043


I can add the above values in B21 to B45 if they can not be added to the script

this VBA is not working with Microsoft 365
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$D$9" and Target.Value = 1205 Then Range("F9:F10").ClearContents
End Sub

This VBA is working with MIcrosioft365
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("d:d")) Is Nothing Then
If Target = 1205 Then
Range("F" & Target.Row).Clear
End If
End If
End Sub

i'm using what is working

 

[Joe4]

Are they in cells B21:B45 on the SAME sheet that you are deleting from, or on a different sheet?
Do you only want to look ONLY at row 9, or other rows too?

 

[Joe4]

If it is on the same sheet, and that you want it to apply to ANY row (like the second code that you said works does), then this should do what you want:

Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Not Intersect(Target, Range("D:D")) Is Nothing Then
        If Application.WorksheetFunction.CountIf(Range("B21:B45"), Target.Value) > 0 Then
            Range("F" & Target.Row).Clear
        End If
    End If
    
End Sub

 

[SIPRIAN]

Thank you , this works

 

[Joe4]

You are welcome.
Glad I was able to help!