Call method based on cell change/value

[j.lancaster1]

Hello,

I am attempting to have my macro run anytime the value of B1 changes. If B1 = 1, I want to run (just for example) Sub x, if B1 = 2, run Sub y, if B1 = 3, run Sub z. Here's my code so far, which does not include the B1 value at all, just if B1 changes, run macro. I'm a noob at this, but I think I'm looking for an "And" clause to add on to each of my If statements.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub
If Target.Address = "$B$1" Then Run "Sheet1.x"
If Target.Address = "$B$1" Then Run "Sheet1.y"
If Target.Address = "$B$1" Then Run "Sheet1.z"

Any help would be appreciated

[Leith Ross]

Hello j.lancaster1,

The Select Case statement makes doing this much easier.

Private Sub Worksheet_Change(ByVal Target As Range)

  ' Check that only one cell was selected and it is in column "B".
    If Target.Cells.Count > 1 Then Exit Sub
    If Intersect(Target, Columns("B:B")) Is Nothing Then Exit Sub

  ' Cell must be a number, if not, exit.
    If Not IsNumeric(Target) Then Exit Sub

        Select Case Target
            Case 1: Call Sub x
            Case 2: Call Sub y
            Case 3: Call Sub z
        End Select

End Sub

[j.lancaster1]

Wow, that makes much more sense. Thank you!

[Leith Ross]

Hello j.lancaster1,

You're welcome.