VB Code to erase two cells as data changes in another

**Nitefox** · 09-29-2013, 10:21 PM

Im looking for code that will monitor the E column and whenever the user selects Return from the drop down list it checks to see if there is data in the corresponding I or J cell (on the same row). If so a message comes up letting them know this action will erase data in the 'Item Exchange' and Exchange Unit' cells (I and J). If the user clicks ok it erases those cells, if they cancel the E column goes back to whatever value it started with.
Thanks,
James

**HaHoBe** · 09-30-2013, 12:28 AM

Hi, James,

usually you could use the Worksheet_Change-event for that but I think you already have used that event for monitoring other cells so you would need to integrate the code there as well.

On it´s own the code may look like (please notice that there may only be one event of each name behind each single sheet):

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count = 1 Then
  With Target
    If .Column = 5 And .Row > 5 Then
      If .Value = "Return" Then
        If .Offset(0, 4).Value <> vbNullString Or .Offset(0, 5).Value <> vbNullString Then
          If MsgBox("This action will erase data in the 'Item Exchange' and 'Exchange Unit' cells", vbOKCancel) = vbOK Then
            Application.EnableEvents = False
            .Offset(0, 4).Value = vbNullString
            .Offset(0, 5).Value = vbNullString
            Application.EnableEvents = True
          Else
            Application.EnableEvents = False
            Application.Undo
            Application.EnableEvents = True
          End If
        End If
      End If
    End If
  End With
End If
End Sub

Your exisiting procedure must be rewritten in order to accept this case as well.

Ciao,
Holger