VBA Code to Execute When Cell Value is Changed

OilEconomist

Active Member
Joined
Dec 26, 2016
Messages
421
Office Version
  1. 2019
Platform
  1. 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:
reply to post #1 EnableEvents are turned off more likely for your macro to work in Sub Worksheet_Change
 
Upvote 0

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top