OilEconomist
Active Member
- Joined
- Dec 26, 2016
- Messages
- 426
- Office Version
- 2019
- Platform
- Windows
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.
VBA Code:
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
Last edited: