Worksheet_Change(ByVal Target As Range)

[hartleyj]

Hello,
I m trying to use the Change by Validation feature. When Cell B4 is changed to "YES" I want Cell B12 to change to yes. Both cells B4 and B12 are data list but I didn't think that would matter? Can you please take a look and let me know what I am doing wrong?

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$4" Then
If Range("B4").Value = "YES" Then
Range("b12").Value = "yes"

Else


End If

Application.EnableEvents = True

End If

End Sub

 

[wigi]

That should work if you have the uppercase and lowercase right.

This works fine for me:

Private Sub Worksheet_Change(ByVal Target As Range)
    With Target
        If .Address = "$B$4" Then
            If .Value = "yes" Then Range("B12").Value = .Value
        End If
    End With
End Sub

 

[kpark91]

Hi, I think the code may be in infinite loop because you're constantly 'changing' the value.

So, just to add

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    With Target
        If .Address = "$B$4" Then
            If .Value = "yes" Then Range("B12").Value = .Value
        End If
    End With
    Application.EnableEvents = True
End Sub

 

[wigi]

Hi, I think the code may be in infinite loop because you're constantly 'changing' the value.

No. B12 is changed and B4 is checked.

 

[kpark91]

No. B12 is changed and B4 is checked.

Right XD

 

[hartleyj]

Thank you all for your feed back. I used the code below and it's working great.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
With Target
If .Address = "$B$4" Then
If .Value = "yes" Then Range("B12").Value = .Value
End If
End With
Application.EnableEvents = True
End Sub

 

[wigi]

Thank you all for your feed back. I used the code below and it's working great.

You're welcome