Why does VBA seem to have a memory?

[XrayLemi]

I have this code in a workbook.

Dim cr As Range
     Set cl = Range("H6:H5000")
     Set cl = Intersect(Target, cl)
     For Each cl In Target
     Select Case True
     Case 8 = cl.Column 'H
     If cl.Value <> "" Then
        Check = MsgBox("Is this entry correct?" & vbCrLf & "This cell cannot be edited after entering a value.", vbYesNo + vbQuestion, "Cell Lock Notification")
            If Check = vbYes Then
            Target.Rows.EntireRow.Locked = True
            Else
            Cells(cr.Row, "H").Value = ""
            End If
          End If
      Case Else
    End Select
   Next cl
  Application.EnableEvents = True
End Sub

Everything works as it is coded. However, If I change the range to ("L6:L5000") and anything associated with the range, the message box STILL appears when you enter a value in column H. It should appear after an entry in column L. I have even copied the code to word, changed the code in Word, erased the old code, made a copy of the sheet, re-pasted the new code, and it still refers back to the old range!
Why? What am I missing?
Thanks in advance,
Jim

 

[Fluff]

For a start these two lines are redundant (ie they don't do anything)

    Set cl = Range("H6:H5000")
     Set cl = Intersect(Target, cl)

Can you post the entire code.

 

[XrayLemi]

Good morning Fluff. Here it is.

Private Sub CommandButton1_Click()
    UpdateDataFromMasterFile
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
  Dim r As Range, c As Range
  Set r = 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 7 = c.Column 'G
        If c.Value = "" Then
          Cells(c.Row, "H").Value = ""
          Cells(c.Row, "H").Locked = True
          Else
          Cells(c.Row, "H").Locked = False
        End If
      Case Else
    End Select
  Next c
  End If
   If Target.Cells.Count > 4 Then Exit Sub
  If Not Intersect(Target, Range("B6:B5000")) Is Nothing Then
    With Target(1, 4)
     .Value = Date
     .EntireColumn.AutoFit
    End With
    End If
     Dim p As Range
     Set p = Range("L6:L5000")
     Set p = Intersect(Target, p)
     For Each p In Target
     Select Case True
     Case 8 = p.Column 'L
     If p.Value <> "" Then
        Check = MsgBox("Is this entry correct?" & vbCrLf & "This cell cannot be edited after entering a value.", vbYesNo + vbQuestion, "Cell Lock Notification")
            If Check = vbYes Then
            Target.Rows.EntireRow.Locked = True
            Else
            Cells(p.Row, "L").Value = ""
            End If
          End If
      Case Else
    End Select
   Next p
  Application.EnableEvents = True
End Sub

 

[Fluff]

Try it like

   Dim p As Range
   Set p = Range("L6:L5000")
   Set p = Intersect(Target, p)
   For Each c In p
      Select Case True
         Case 12 = p.Column 'L
            If c.Value <> "" Then
               Check = MsgBox("Is this entry correct?" & vbCrLf & "This cell cannot be edited after entering a value.", vbYesNo + vbQuestion, "Cell Lock Notification")
               If Check = vbYes Then
                  c.EntireRow.Locked = True
               Else
                  Cells(c.Row, "L").Value = ""
            End If
         End If
         Case Else
      End Select
   Next c

 

[XrayLemi]

Fluff,
When I copy the code I get a runtime 424 object required error. When I hit debug, the line of code: For Each c In p highlights in yellow.

 

[Fluff]

Try adding this line

   Set p = Intersect(Target, p)
   If p Is Nothing Then Exit Sub
   For Each c In p

 

[XrayLemi]

Okay, now it compiles with no errors but.... The message box does not come up and the row does not lock.

 

[Fluff]

How are you changing the values in col L?

 

[XrayLemi]

It is a simple name entry. No pull downs, no copy / paste.

 

[Fluff]

So they are manually entered, one cell at a time?