Hi,

I struggled to follow this code because I'm not sure where the code is i.e.
which sheet. However the reason it's re-starting is because you need to
disable events while the code is running because every time you change the
sheet the code re-calls itself and you start all over again from the
beginning. Try this change.

Also dimension the variables
dim lastcl as long
dim lastcl2 as long


Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
If Range("D64").Value < "" And Range("U33").Value < "" Then
Application.EnableEvents = False
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
Application.EnableEvents = True
Exit Sub
End If
Range("U33").ClearContents
Application.EnableEvents = True
End Sub
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"K" wrote:

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
.