Using VBA to change one cell if another one does...?

[Applewine]

I am working on a scheduler that will, if a shift is entered in one column, first check if there is an employee listed on that row...if no employee is on that row, the entry will be deleted (with an accompanying MsgBox alert)..

My code does what it is supposed to, but loops forever!

Private Sub Worksheet_Change(By Val Target As Excel.Range)
Dim VRange As Range
Set VRange = Range("B6:B14") 'This is column for start of shift
If Not Intersect(Target, VRange) Is Nothing Then
If Target.Offset(0,-1) = "" Then 'Check employee name column...
MsgBox"There is no employee for that shift...entry deleted"
Target = "" 'Erase entry in shift column
End If
End If
End Sub


Any ideas?...I must use VBA for this BTW

Thanks, Dave

[Norie]

Well since your existing code changes your worksheet the Change event will get triggered by it, hence the loop.

You need to turn events off.

Application.EnableEvents = False

Then run the code and switch them back on.

Application.EnableEvents = True