VBA Case Statement: Continous calculations

[AustinRoberts32]

I have a Case statement running with a worksheet_change event. I noticed that anytime a change is done it go completely through the entire
worksheet step by step through the module. The worksheet has 75k rows so it literally takes forever. Is there a way to make this procedure
only do the active cell that was changed?
________________________________________________________________________________________________________________________

Here is the my worksheet_change module I am calling the sub from:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

    Application.EnableEvents = False

  Call ChangeStatus

    Application.EnableEvents = True

End Sub

And here is the entire module that is being called:

Public Sub ChangeStatus()
Application.EnableEvents = False
    
    Dim LastRow As Long
    Dim i As Long
      LastRow = Range("B" & Rows.Count).End(xlUp).Row
      For i = 2 To LastRow
        Select Case LCase(Range("B" & i))
          Case "no"
            Range("I" & i) = "Incomplete"
          Case "yes"
            Range("I" & i) = "Already Finished"
          Case "nstk"
            Range("I" & i) = "NSTK"
          Case "obsl"
            Range("I" & i) = "OBSLETE"
          Case "ordered"
            Range("I" & i) = "Follow Up"
        End Select
      Next i
      
Application.EnableEvents = True
      
      
Application.EnableEvents = False
    
    Dim LastRow2 As Long
    Dim ii As Long
      LastRow2 = Range("H" & Rows.Count).End(xlUp).Row
      For ii = 2 To LastRow
        Select Case LCase(Range("H" & ii))
          Case "x"
            Range("I" & ii) = "Completed"
        End Select
      Next ii
      
Application.EnableEvents = True
      
          
Application.EnableEvents = False
    
    Dim LastRow3 As Long
    Dim iii As Long
      LastRow3 = Range("I" & Rows.Count).End(xlUp).Row
      For iii = 2 To LastRow
        Select Case LCase(Range("I" & iii))
          Case "completed"
            Range("J" & iii) = Date
          Case "follow up"
            Range("L" & iii) = Date
          Case "incomplete"
            Range("I" & iii) = " "
          Case "obselete"
            Range("I" & iii) = " "
          Case "nstk"
            Range("I" & iii) = " "
        End Select
      Next iii
 
      
          
 Application.EnableEvents = True
    
 End Sub

Excel will run through he first Statement and check the 75k, then continue on to the second Case Statement for all 75k.

Also please excuse my sloppy structure and nasty code. I just started learning VBA in my spare time.

Thanks!

 

[Puertorekinsam]

If you put this if statement around your code, it will changed cell is the one above or to the left of the changed cell... assuming you pressed enter or tab to exit the cell. It's not perfect, but an option.

If Target.Address = ActiveCell.Offset(1, 0).Address Or Target.Address = ActiveCell.Offset(0, 1).Address Then


end if

 

[Fluff]

Which columns do you want this to trigger on?