How can I fire a Macro ONLY if someone deletes a value from a certain Range?

Question

I have the script below that pretty much does what I want, but I need it to fire off ONLY if someone deletes a value from the Range E9:E17. It is not really a SelectionEvent, it is more of a Change_Event, but if the change is the delete, the value is gone from the cell before I can capture it. I think the SelectionEvent has to call the change event. Does it make sense? Let me know if you need clarification. Thanks!!

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

Application.EnableEvents = True

If Not Intersect(Target, Range("E9:E17")) Is Nothing Then

    GetValue = ActiveCell.Value
    GetCustomer = ActiveCell.Offset(0, -1).Value

    With Sheets("LargeCustomerOP").Range("D2:D6") 'searches range in Col D
        Set Rng = .Find(What:=GetCustomer, _
                        After:=.Cells(.Cells.Count), _
                        LookIn:=xlValues, _
                        LookAt:=xlWhole, _
                        SearchOrder:=xlByRows, _
                        SearchDirection:=xlNext, _
                        MatchCase:=False)
        If Not Rng Is Nothing Then
            Rng.Offset(0, 1).Value = Rng.Offset(0, 1).Value + GetValue
        Else
            'value not found
        End If
    End With
End If

Application.EnableEvents = True

End Sub

Well, I thought of that, but I'm not sure what it would look like. If it is empty, the opportunity to capture the value is gone. If it's not empty, there is nothing to do, right. If you have a working solution, please post the code. — ASH, Jun 21 '17 at 14:55
You haven't described precisly what your chain of events needs to do. — jkpieterse, Jun 21 '17 at 14:57

score 3 · Answer 1 · answered Jun 21 '17 at 14:58

3

Save the data in the Worksheet_SelectionChange-Event in a global var and access it in the Worksheet_Change-Event. See this example on StackOverflow

answered Jun 21 '17 at 14:58

FunThomas

23,043
3
18
34

Now it makes sense. Thanks everyone!! – ASH Jun 21 '17 at 16:01

score 0 · Answer 2 · answered Jun 21 '17 at 15:06

Sorry, don't have time to write the code but I'm sure someone else can help there..

Create a mirror copy of the sheet on a hidden sheet by using Worksheet_Change. you only need to mirror the range you're worried about.

Each time a change is made perform the following in the event:

Check if the Target becomes blank. If it does, store the value of the same cell from the mirror.
Copy the value of the Target cell to the same cell on the mirror.
If the store has a value, then act upon it as you see fit.

How can I fire a Macro ONLY if someone deletes a value from a certain Range?

2 Answers2