Hi all, I got macro below which checks values of "E" column with the
values of "A" column of Sheets("Parked CC"). And if value match then
a message box pops up which have two buttons "OK" and "Cancel". What
i am trying to do in below macro that when user click "OK" button it
should clear ActiveCell value but if user click "Cancel" button then
it should do nothing and move to next matched value cell. The problem
i am getting in below macro that when i am pressing one of the button
in message box it works ok and move to next cell but as soon as i
press other button it starts again from the top instead of moving to
next cell. I just want it to loop once even if i press different
button in the middle of the process. Hope i was able to explain my
question. Please can any frined can help me in this


Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
If Range("D64").Value < "" And Range("U33").Value < "" Then
lastcl = Sheets("Parked CC").Cells(Rows.Count, "A").End(xlUp).Row
lastcl2 = Me.Cells(Rows.Count, "D").End(xlUp).Row
For Each c In Range("E64:E" & lastcl2).Cells
If Application.CountIf(Sheets("Parked CC").Range("A2:A" & lastcl), c)
< 0 Then
ANS = MsgBox("Cost Centre " & c.Value & " has been closed" &
vbNewLine & "Press OK to clear" & vbNewLine & "Press CANCEL to
ignore", vbOKCancel + vbInformation, "Cost Centre Check!")
c.Select
If ANS = vbOK Then
ActiveCell.ClearContents
Else
End If
End If
Next
Else
Exit Sub
End If
Range("U33").ClearContents
End Sub