Hi, I want K to clear every time there is an update whether it is changing value or delete value (blank). I tested single code in separate sheet and it work fine, but when I put all my codes together. COL K only clear when I make change to W:AP but when I delete value from W:AP, COL K doesn't clear.
Thank you!
Private Sub Worksheet_Change(ByVal Target As Range)
'Locked sheet
'Locked Cell after verified
If Not Intersect(Target, Columns(11)) Is Nothing Then
If Target.Value = "Completed" Then
ActiveSheet.Unprotect ("PASSWORD")
Range("U" & Target.Row & ":AP" & Target.Row).Locked = True
ActiveSheet.Protect ("PASSWORD")
Else
ActiveSheet.Unprotect ("PASSWORD")
Range("U" & Target.Row & ":AP" & Target.Row).Locked = False
ActiveSheet.Protect ("PASSWORD")
End If
End If
'Prevent Duplicate Name
If Target.Cells.Count > 1 Or IsEmpty(Target(1, 1)) Then Exit Sub
If Not Intersect(Target, Range("W:AP")) Is Nothing Then
If WorksheetFunction.CountIf(Range("W" & Target.Row & ":AP" & Target.Row), Target) > 1 Then
MsgBox Target & " already exists. Please select new name."
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
End If
End If
'Clear verification if record changes
Dim rLook As Range, r As Range, Intr As Range
Set rLook = Range("W:AP")
Set Intr = Intersect(rLook, Target)
If Intr Is Nothing Then Exit Sub
Application.EnableEvents = False
For Each r In Intr
If r.Value = "" Then
rw = r.Row
ActiveSheet.Unprotect ("PASSWORD")
Range("K" & rw).ClearContents
End If
Next r
Application.EnableEvents = True
For Each r In Intr
If r.Value <> "1" Then
rw = r.Row
ActiveSheet.Unprotect ("PASSWORD")
Range("K" & rw).ClearContents
End If
Next r
Application.EnableEvents = True
End Sub
Bookmarks