Code seems to be searching backwards.

[XrayLemi]

Hello again all. Hope you are safe and healthy.
I have the following code on multiple sheets in a workbook. I cannot use XL2BB because of security reasons. so I will have to post it the hard way.

Private Sub CommandButton1_Click()
    UpdateDataFromMasterFile
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
  Dim r As Range, c As Range
  Set r = Union(Range("J6:J5000"), Range("G6:G5000"))
  Set r = Intersect(Target, r)
  If Not r Is Nothing Then
   Application.EnableEvents = False
   For Each c In r
    Select Case True
       Case 10 = c.Column 'J
        If c.Value = "" Then
          Cells(c.Row, "L").Value = ""
          Cells(c.Row, "L").Locked = True
          Else
          Cells(c.Row, "L").Locked = False
        End If
       Case 7 = c.Column 'G
        If c.Value = "Not Listed" Then
          Cells(c.Row, "H").Locked = False
          Else
          Cells(c.Row, "H").Locked = True
          Cells(c.Row, "H").Value = ""
        End If
       Case Else
    End Select
   Next c
  End If
  
If Target.Cells.Count > 3 Then Exit Sub
  If Not Intersect(Target, Range("C6:C5000")) Is Nothing Then
   With Target(1, 3)
    .Value = Date
    .EntireColumn.AutoFit
   End With
  End If
 
    Dim p As Range, z As Range
     Set p = Range("M6:M5000")
     Set p = Intersect(Target, p)
     If Not p Is Nothing Then
   Application.EnableEvents = False
     For Each z In p
      Select Case True
       Case 13 = z.Column 'M
        If z.Value <> "" Then
         Check = MsgBox("Are your entries correct?" & vbCrLf & "After entering yes, These values CANNOT be changed.", vbYesNo + vbQuestion, "Cell Lock Notification")
           If Check = vbYes Then
            Target.Rows.EntireRow.Locked = True
            Cells(z.Row + 1, "B").Locked = False
            Cells(z.Row + 1, "C").Locked = False
            Cells(z.Row + 1, "D").Locked = False
            Cells(z.Row + 1, "F").Locked = False
            Cells(z.Row + 1, "G").Locked = False
            Cells(z.Row + 1, "I").Locked = False
            Cells(z.Row + 1, "J").Locked = False
            Cells(z.Row + 1, "K").Locked = False
            Cells(z.Row + 1, "M").Locked = False
            If Cells(Application.ActiveCell.Row, 17).Value <> "" Then Copyemail 'Q
            If Cells(Application.ActiveCell.Row, 18).Value <> "" Then ThisWorkbook.Save 'R
            With Me
                .Parent.Activate
                .Activate
                .Range("B" & Rows.Count).End(xlUp).Offset(1).Activate
            End With
           Else
            Cells(z.Row, "M").Value = ""
           End If
          End If
      Case Else
    End Select
   Next z
 End If
      Application.EnableEvents = True
    
End Sub

This line of code is giving me a headache.

 If Cells(Application.ActiveCell.Row, 17).Value <> "" Then Copyemail 'Q

If the active row column 17 cell is blank, nothing should happen. If the active row column 17 cell has a value, the module Copyemail should run. BUT, it should only run for the active row you are working on and only if the column 17 cell has an entry.

What I am finding is there are times when the code will at random, go BACK to find the last row that has a column 17 entry, and run the module. This happens even if the column 17 cell in the active row is blank. It also happens on random computers. This should not be happening.

I know this is long winded but I am trying to be thorough.

Thank you in advance,
Jim

 

[XrayLemi]

Thank you very much for the help and the lesson!

 

[JLGWhiz]

Thank you very much for the help and the lesson!

Yoou're welcome,
regards, JLG