Why is If, Then VBA statement not executing?

Hello Experts,
I am not able to get the following code to execute when either cell, J2 or K2 values change.  I am not receiving any errors, just no execution.  Any ideas why?


Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim rngTerm1 As Range, rngTerm2 As Range, rngUnion As Range
       
       
    ' The variable rngUnion contains the cells that, if either is changed, will
    ' update the record count for retained students according to the term specified.
    Set rngTerm1 = Range("J2")
    Set rngTerm2 = Range("K2")
    Set rngUnion = Union(Range("J2"), Range("K2"))

    If Not Application.Intersect(rngUnion, Range(Target.Address)) Is Nothing Then
    
        'for changing the formulas to reflect fall to fall terms
        If rngTerm1 = 10 And rngTerm2 = 10 Then
            
    Range("H23:H118").Select
        Selection.Replace What:="SPRING", Replacement:="FALL", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

        
        'for changing the formulas to reflect spring to spring terms
        ElseIf rngTerm1 = 20 And rngTerm2 = 20 Then
            
    Range("H23:H118").Select
        Selection.Replace What:="FALL", Replacement:="SPRING", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

          
        'for changing the formulas to reflect spring to fall terms
        ElseIf rngTerm1 = 20 And rngTerm2 = 10 Then
            
            
    Range("H23:H30,H39:H46,H55:H62,H71:H78,H87:H94,H103:H110").Select
        Selection.Replace What:="SPRING", Replacement:="FALL", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    
    Range("H26,H30,H42,H46,H58,H62,H74,H78,H90,H94,H106,H110").Select
        Selection.Replace What:="GRADUATION GROUP", Replacement:="GRADUATION - FALL GROUP", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        
    Range("H26,H30,H42,H46,H58,H62,H74,H78,H90,H94,H106,H110").Select
        Selection.Replace What:="GRADUATION - SPRING GROUP", Replacement:="GRADUATION - FALL GROUP", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        
    Range("H31:H38,H47:H54,H63:H70,H79:H86,H95:H102,H111:H118").Select
        Selection.Replace What:="FALL", Replacement:="SPRING", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    
    Range("H34,H38,H50,H54,H66,H70,H82,H86,H98,H102,H114,H118").Select
        Selection.Replace What:="GRADUATION GROUP", Replacement:="GRADUATION - SPRING GROUP", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        
    Range("H34,H38,H50,H54,H66,H70,H82,H86,H98,H102,H114,H118").Select
        Selection.Replace What:="GRADUATION - FALL GROUP", Replacement:="GRADUATION - SPRING GROUP", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        
    
                       
        'for changing the formulas to reflect fall to spring terms
        ElseIf rngTerm1 = 10 And rngTerm2 = 20 Then
            
    Range("H23:H30,H39:H46,H55:H62,H71:H78,H87:H94,H103:H110").Select
        Selection.Replace What:="FALL", Replacement:="SPRING", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        
    Range("H26,H30,H42,H46,H58,H62,H74,H78,H90,H94,H106,H110").Select
        Selection.Replace What:="GRADUATION GROUP", Replacement:="GRADUATION - SPRING GROUP", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        
    Range("H26,H30,H42,H46,H58,H62,H74,H78,H90,H94,H106,H110").Select
        Selection.Replace What:="GRADUATION - FALL GROUP", Replacement:="GRADUATION - SPRING GROUP", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        
    Range("H31:H38,H47:H54,H63:H70,H79:H86,H95:H102,H111:H118").Select
        Selection.Replace What:="SPRING", Replacement:="FALL", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    
    Range("H34,H38,H50,H54,H66,H70,H82,H86,H98,H102,H114,H118").Select
        Selection.Replace What:="GRADUATION GROUP", Replacement:="GRADUATION - FALL GROUP", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        
    Range("H34,H38,H50,H54,H66,H70,H82,H86,H98,H102,H114,H118").Select
        Selection.Replace What:="GRADUATION - SPRING GROUP", Replacement:="GRADUATION - FALL GROUP", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
           
        Else
                End If
            End If
        End Sub

Open in new window


Many Thanks in Advance,

Lindsay
Lindsay_KAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saurabh Singh TeotiaCommented:
The code works for me without any problem..I'm assuming you have this code in the sheet module only...And of the correct sheet where you are working..

Even after that if it doesn't work that means you don't have events enable in excel..Just enable them by running the below mentioned code...

Sub X()
        Application.EnableEvents = True
        
        End Sub

Open in new window


What this do will enable events in your excel which should make this code trigger once you make any changes in your cell

Saurabh...
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Martin LissOlder than dirtCommented:
Providing that the event is actually triggered (and if not then Saurabh's suggestion will fix that), you may find the techniques described in my article on debugging useful in finding the problem.

Oh, and note that the change event isn't fired until you actually leave the cell.
0
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
On another note, you should contract
 Range("H34,H38,H50,H54,H66,H70,H82,H86,H98,H102,H114,H118").Select
        Selection.Replace '...

Open in new window

to something like
 Range("H34,H38,H50,H54,H66,H70,H82,H86,H98,H102,H114,H118").Replace ' ...

Open in new window

as using selections is prone to errors, and changes the current selection even if that intercepts with user interaction. And first selecting the ranges you want to work on in VBA is unnecessary.
And if you wish to apply several operation to the same range, WITH is your friend:
with  Range("H34,H38,H50,H54,H66,H70,H82,H86,H98,H102,H114,H118")
   .Replace '...
   .Replace '...
end with

Open in new window

0
Lindsay_KAuthor Commented:
Thanks so much to all of you,
I got it going again with the incorporation of Saurabh and Qlemo's solutions, along with some fixes in my formulas.  I believe the Application.EnableEvents = True was the fundamental fix, with Qlemo's solution allowing the code to run immediately instead of getting hung up and appearing not to execute.

Many thanks again!

-Lindsay
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.