Hi, Thank you for this site! I've learned so much just reviewing the posts and challenging myself to write better and more increasingly more complex code.
Here's what I have:
-All Cells are Unlocked and Sheet is Protected with no password
-Cells I6:L14 MERGED across each row (I6:L6, I7:L7, I8:L8, etc.)
-When I6 (merged I6:L6) is BLANK, Then ClearContents of Cells M6:O6
-I6 DropDown includes a BLANK Line
WITH I6:L6 Merged
-Selecting BLANK in I6:L6 (Merged) DropDown WORKS (Clears Contents of M6:O6)
-Using DELETE KEY in I6:L6 (Merged) DOES NOT WORK (contents of M6:O6 not cleared)
-Using DropDown BLANK - Address shows $I$6 and $I$6:$L$6, respectively
-Using DELETE KEY - No Messages are shown.
WITH I6:L6 NOT Merged (using only I6 as the Target)
-Both Dropdown BLANK & DEL Key work fine
-Address shows $I$6 for both
After much research over a couple of days I can't figure out how to make this work using the DEL Key and would appreciate any help.
Here's what I have:
-All Cells are Unlocked and Sheet is Protected with no password
-Cells I6:L14 MERGED across each row (I6:L6, I7:L7, I8:L8, etc.)
-When I6 (merged I6:L6) is BLANK, Then ClearContents of Cells M6:O6
-I6 DropDown includes a BLANK Line
WITH I6:L6 Merged
-Selecting BLANK in I6:L6 (Merged) DropDown WORKS (Clears Contents of M6:O6)
-Using DELETE KEY in I6:L6 (Merged) DOES NOT WORK (contents of M6:O6 not cleared)
-Using DropDown BLANK - Address shows $I$6 and $I$6:$L$6, respectively
-Using DELETE KEY - No Messages are shown.
WITH I6:L6 NOT Merged (using only I6 as the Target)
-Both Dropdown BLANK & DEL Key work fine
-Address shows $I$6 for both
After much research over a couple of days I can't figure out how to make this work using the DEL Key and would appreciate any help.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
' ClearContents of offset cells if Target is BLANK or "Not Used"
If Not Intersect(Target, Range("I6:L14")) Is Nothing Then
Application.EnableEvents = False
MsgBox Target.Address
MsgBox Target.MergeArea.Address
If Target = "" Or Target.Value = "Not Used" Then
MsgBox "Target is Blank"
For i = 1 To 5
Target.Offset(0, i).ClearContents
Next i
End If
ActiveSheet.Protect
Application.EnableEvents = True
End If
End Sub
Last edited: