Execute a subroutine when a user enters a trigger into a cell

Question

Example data in Excel:

   A    B    C  
1  9         5  
2  4    y    3  
3  1         9  
4  66        4  
5  5         9  

What I want to do is when I enter Y in Column B, I want "somestuff" to execute.

1. I don't think If Active.Cell = Y will work here, because when I enter Y and press enter, the active.cell will not be the one I just entered Y in.

2. Looping through Column B will not work because

   a. There will be multiple Ys in the column

   b. I need to execute "somestuff" right after entering a Y into a cell.

Could you please suggest what should I try?

Answer 1

As siddarth suggested, Worksheet_change() is what you are looking for. Here is how you may achieve your task without any leaks. Paste this code inside the sheet in which you are editing the y value.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim MyRng As Range
Set MyRng = Range("B:B")

Dim PieRng As Range
'Intersect will ensure your current cell lies on column B
Set PieRng = Intersect(Target, MyRng)

'if conditions to ensure trigger code only one cell edited on Col B and is 'y/Y'.
If Target.Columns.Count = 1 And Target.Rows.Count = 1 Then
    If Not PieRng Is Nothing And LCase(Target.Text) = "y" Then
       'Do my stuff here when y / Y are entered in Column B of current sheet
       MsgBox "You entered " & Target.Value & " in Col B"
    End If
End If


End Sub

Let us know if it fails...

Answer 2

I finished what I was working that involved this question. Thought I would share the final product. Here is what the VBA does:
1) Retrieve the address & value of the cell next to a cell where a "y" has been entered.
2) Find the same value in a different column and return its address.
3) Make that address the active cell.

Code follows:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim PostRng As Range
Dim PendRng As Range
Dim rValue As Range
Dim lLoop As Long
Dim rFoundCell As Range
Dim PieRng As Range

Set PostRng = Range("B:B")
Set PendRng = Range("D:D")

'"Intersect" will ensure your current cell lies on correct column.
 Set PieRng = Intersect(Target, PostRng)

'This block will return the range & value of the cell one column to the left of the column where "y" or "Y" are entered.
'IF conditions to trigger code.
If Target.Columns.Count = 1 And Target.Rows.Count = 1 Then
    If Not PieRng Is Nothing And LCase(Target.Text) = "y" Then
      'Do my stuff here when y / Y are entered in Column B of current sheet
     Set rValue = Target.Offset(0, -1)
     ' MsgBox "You entered " & rValue.Value

'This will loop through a different column, to find the value identified above, and return its cell address in the other column.
     With PendRng
       Set rFoundCell = .Cells(1, 1)
        For lLoop = 1 To WorksheetFunction.CountIf(.Cells, rValue.Value)
            Set rFoundCell = .Find(What:=rValue.Value, _
                           After:=rFoundCell, _
                           LookIn:=xlValues, _
                           LookAt:=xlPart, _
                           SearchOrder:=xlByRows, _
                           SearchDirection:=xlNext, _
                           MatchCase:=False)
'            MsgBox "val: " & rValue.Value & "   Matching Cell: " & rFoundCell.Address

'This will use the cell address identified above to move the active cell to that address.
'Have to convert the address to row/column to use in Cell.Select.
            Cells(Range(rFoundCell.Address).Row, Range(rFoundCell.Address).Column).Select

    Next lLoop
    End With
End If
End If  
End Sub