Results changing from a drop down selection - understanding VBA code

[jgold20]

I just want to make sure what I am trying to accomplish will not work:

1. I have a drop down box & when you select "YES" it works.
2. I have added code to take a different action if the response is "NO".
3. When it is "NO" and you enter a number in A3 & B10, the result no longer shows in C10 (it is blank - the same holds true for b11 - b14 & c11 - c14), however, the multiplication does happen as the other cells have the correct results from the calculation. If I remove the code for ClearContents, and I enter a number in b10 - b14, id I select "NO", c10 - c14 does have the intended results.
4. Is there a way to get this to work without populating b10 - b14 and than selecting "NO"? Or should I just use IF logic in the cell?

Private Sub Worksheet_Change(ByVal Target As Range)


' Check to see if cell I3 updated
If Target.Address <> "$I$3" Then Exit Sub


Application.EnableEvents = False


If Target.Value = "YES" Then
Range("b10:b14").Value = Range("g3:g7").Value
Range("c10:c14").Value = Range("f3:f7").Value
ElseIf Target.Value = "NO" Then
'Range("b10:b14").ClearContents
'Range("c10:c14").ClearContents
Range("c10") = Range("a3") * Range("b10")
Range("c11") = Range("a3") * Range("b11")
Range("c12") = Range("a3") * Range("b12")
Range("c13") = Range("a3") * Range("b13")
Range("c14") = Range("a3") * Range("b14")
End If


Application.EnableEvents = True


End Sub

[Paul_Hossler]

If it's NO and you want a change in A3 to update C10 etc, you might might need to use formulas

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

    ' Check to see if cell I3 updated
    If Target.Address <> "$I$3" Then Exit Sub

    Application.EnableEvents = False
    If Target.Value = "YES" Then
        Range("b10:b14").Formula = "=G3"
        Range("c10:c14").Formula = "=F3"
    
    ElseIf Target.Value = "NO" Then
            'Range("b10:b14").ClearContents
            'Range("c10:c14").ClearContents
            Range("c10").Formula = "=$A$3*B10"
            Range("c11").Formula = "=$A$3*B11"
            Range("c12").Formula = "=$A$3*B12"
            Range("c13").Formula = "=$A$3*B13"
            Range("c14").Formula = "=$A$3*B14"
    End If
    
    Application.EnableEvents = True
End Sub