Hello,

I have this code to search the workbook when entering text or amount in cell N4. It searches and lists all occurrences that match fine when the workbook is unprotected. I set the column 'P' to be unlocked but when running a search it locks the column cells and results in debug when running it again.

Can someone please see if there is a way to keep column 'P' cells from being locked?

Thank you,
Joe

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim R As Long, C As Long, Data As Variant
  Dim FindMe As String, Answer As String, WSnCELL() As String
  Dim Cell As Range, Ws As Worksheet
  If Target.Address(0, 0) = "N4" Then
    If Len(Target.Value) > 0 Then
      FindMe = Format(Target.Value)
      For Each Ws In ThisWorkbook.Worksheets
        Data = Ws.Range("A1", Ws.Cells(Ws.Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row, Ws.Cells.Find("*", , xlValues, , xlByColumns, xlPrevious).Column))
        For R = 1 To UBound(Data, 1)
          For C = 1 To UBound(Data, 2)
            If Format(Data(R, C)) = FindMe Then Answer = Answer & Chr(1) & Ws.Name & " - " & Cells(R, C).Address(0, 0)
          Next
        Next
      Next
      WSnCELL = Split(Mid(Answer, 2), Chr(1))
      Range("P4:P" & Rows.Count).Clear
      Range("P4").Resize(UBound(WSnCELL) + 1) = Application.Transpose(WSnCELL)
    Else
      Range("P4:P" & Rows.Count).Clear
    End If
  End If
End Sub