I have a code (thanks royUK and Leath!) where, if column E is "Annuity", you can type a value in column J and it runs a formula to calculate a value for column K. If E is not "Annuity" it clears the contents of J (which should clear K). What's not working is when column J gets cleared or manually deleted, column K must be blank. The worksheet_change event is not working for deletion of column J.
It also isn't reprotecting the sheet after I delete a value in column J. I've tried pasting the .Protect argument in a bunch of different places and can't get it to work. I need it to reprotect no matter what is typed in or deleted. Here's the code, and I've attached the workbook as well. Thanks!
Private Sub Worksheet_Change(ByVal Target As Range)
Const pw As String = "blondie"
Dim Rng As Range, c As Range, Rng2 As Range, Rng3 As Range, i As Long, lastrow As Long
Set Rng = Range("E17:E116")
Set Rng2 = Range("J17:J116")
Set Rng3 = Range("K17:K116")
With Sheets("NML Inventory")
.Unprotect pw
If Not Intersect(Target, Rng2) Is Nothing Then
lastrow = Range("J65536").End(xlUp).Row
For i = 17 To lastrow
If .Cells(i, 10) = 0 Or IsEmpty(.Cells(i, 10)) Or .Cells(i, 5) <> "Annuity" Then
Cells(i, 11).ClearContents
Else: Cells(i, 11) = Sheets("INPUT").Cells(28, 13) * Sheets("NML Inventory").Cells(i, 10)
End If
Next
Else
If Not Intersect(Target, Rng) Is Nothing Then
For Each c In Rng
Select Case c.Value
Case "Annuity"
c.Offset(0, 3).ClearContents
c.Offset(0, 4).ClearContents
Case "DI", "LTC"
c.Offset(0, 3).ClearContents
c.Offset(0, 5).ClearContents
Case Else
c.Offset(0, 5).ClearContents
End Select
Next c
.Protect pw, AllowSorting:=True, AllowFiltering:=True
End If
End If
End With
End Sub
Bookmarks