DELETE Key of Merged Cell Content doesn't trigger Worksheet_Change

Bwanna

New Member
Joined
Jan 10, 2018
Messages
13
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.

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:

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

Forum statistics

Threads
1,215,501
Messages
6,125,169
Members
449,212
Latest member
kenmaldonado

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top