Why is this simple "On Change" Macro not working?

[tonywatsonhelp]

Hi Everyone,
very confused,
I thought id done this right but its not working

All i want is for when i select either Yes or No from a drop down box to run one or the other macros?

this is what i wrote, can someone please tel me where i went wrong?

thanks

Tony

Private Sub Worksheet_Change(ByVal Target As Range)



If Target.Address = "$y$26" Then
If Range("Y26:Y27").Value = "Yes" Then
Call zzRedBoxhide
If Range("Y26:Y27").Value = "No" Then
Call zzRedBoxunhide


End If


End If
End If
End Sub

 

[Gary's Student]

Test Y26 and Y27 separately.....you need to distinguish between requiring one cell to have the value "Yes" and requiring two cells to have that value.

 

[mikerickson]

Target.address will never be $y$26, the Y needs to be upper case.

 

[tonywatsonhelp]

made a few tinyadjustments thanks for the advice,
now it runs if the cell = Yes but nothing if it no?
have i missed something here?

Private Sub Worksheet_Change(ByVal Target As Range)



If Target.Address = "$Y$26" Then
If Range("Y26").Value = "Yes" Then
Call zzRedBoxhide
If Range("Y26").Value = "No" Then
Call zzRedBoxunhide


End If


End If
End If
End Sub

 

[mikerickson]

If you indented, it would be easier to see that the If "No" test is inside the If "Yes" test.

Try using an ElseIF construction

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$Y$26" Then
        If Range("Y26").Value = "Yes" Then
            Call zzRedBoxhide
        ElseIf Range("Y26").Value = "No" Then
            Call zzRedBoxunhide
        End If
    End If
End Sub

 

[tonywatsonhelp]

Hi Mikerickson, very good advice, works pefectly,
Thank you very much

Tony