VBA Code to Execute When Cell Value is Changed

[OilEconomist]

Thanks in advance for your assistance. I would like to run the Macro "Find_Vals" when I change an entry in Cell B7, but nothing happens when I do make a change. Please note that it has a pick list (data validation) in B7. The macro "Find_Vals" works when I hit run, but as indicated, I would like it to be automatic when I change cell B7.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$B$7" Then
        Call Find_Vals
    End If
End Sub


'***************************************************************************************************************
Private Sub Find_Vals()

    '__________________________________________________________________________________________________________
    'Dimensioning
  
        Dim LastRow As Long
  
        Dim WS As Worksheet
  
        Dim RngB As Range
        Dim FndRng As Range
  
        Dim FndTckVal As String
        Dim FndCNVal As String


    '__________________________________________________________________________________________________________
    'Code
          
        Set WS = Sheets("Our.Summary")
      
        With Sheets("Our.Summary")
            LastRow = .Cells.Find(What:="*", After:=Cells(1), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            Set RngB = .Range("B9:B" & LastRow)
        End With
      
      
        'Move the cursor to the cell
            With WS
              
                FndTckVal = .Range("B7")
                  
                Set FndRng = RngB.Find(FndTckVal, LookIn:=xlValues)
          
                If Not FndRng Is Nothing Then
                    FndRng.Select
                Else
                    MsgBox FndTckVal & " is not found!"
                End If
      
            End With



End Sub

 

[My Aswer Is This]

You post says A7 but your script says B7

 

[OilEconomist]

You post says A7 but your script says B7

Thank you. It should be B7 as I changed the post to reflect that.

 

[My Aswer Is This]

Thank you. It should be B7 as I changed the post to reflect that.

So does it work now?

 

[My Aswer Is This]

For more help I would need to know in words what your attempting to do.
I do not like to read scripts to see what user is trying to do when the script does not do what you want.

 

[OilEconomist]

So does it work now?

No

 

[OilEconomist]

For more help I would need to know in words what your attempting to do.
I do not like to read scripts to see what user is trying to do when the script does not do what you want.

I have a list of values in a sheet within Range("B9"B" & LastRow). When I change the value in cell B7, I would like it to go to the cell within Range("B9"B" & LastRow) that contains the value I entered into B7. When I run the Macro manually it works, but when I make the change to cell B7, the macro "Worksheet_Change(ByVal Target As Range)" does not execute the Macro "Find_Vals()".

 

[My Aswer Is This]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  10/30/2021  7:43:39 PM  EDT
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
If Target.Address = "$B$7" Then
Dim ans As String
ans = Target.Value
Dim SearchRange As Range
SearchString = ans
Dim lastrow As Long
lastrow = Cells(Rows.Count, "B").End(xlUp).Row
Set SearchRange = Range("B9:B" & lastrow).Find(SearchString, LookIn:=xlValues, lookat:=xlWhole)
If SearchRange Is Nothing Then MsgBox SearchString & "  Not Found": Exit Sub
SearchRange.Select

End If
End Sub

 

[OilEconomist]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  10/30/2021  7:43:39 PM  EDT
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
If Target.Address = "$B$7" Then
Dim ans As String
ans = Target.Value
Dim SearchRange As Range
SearchString = ans
Dim lastrow As Long
lastrow = Cells(Rows.Count, "B").End(xlUp).Row
Set SearchRange = Range("B9:B" & lastrow).Find(SearchString, LookIn:=xlValues, lookat:=xlWhole)
If SearchRange Is Nothing Then MsgBox SearchString & "  Not Found": Exit Sub
SearchRange.Select

End If
End Sub

Thanks My Aswer Is This for your assistance. It still does not work. Not sure what the issue is.

 

[My Aswer Is This]

So when you ran my script what did it do?
You said search for value in Range("B9") to last row with data in Column B and then select that cell.