Hi JE

Many thanks for the prompt reply and based on my description, your code
worked - cheers!

Now that I have run it, I do need seek advice on tweeking the code as follows:


1. By clicking "NO" in the message box, the cell that has "3. Finalized" in
it should clear so that the user knows they must choose another selection
from the drop down list.

2. The automated cut and paste to sheet 2 must retain previously pasted rows -
your code currently overwrites prevous entries - i.e. all finalised rows must
remain in sheet 2.

JE can you Assist?

JE McGimpsey wrote:
One way:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim nResult As Long
With Target
If .Count 1 Then Exit Sub
If Not Intersect(.Cells, Columns(8)) Is Nothing Then
If .Text Like "Finalized" Then
nResult = MsgBox(Prompt:= _
"Clicking yes will move this line to sheet 2", _
Title:="Are you sure?", _
Buttons:=vbYesNo)
If nResult = vbYes Then
With Rows(.Row)
.Copy Destination:=Sheets("Sheet2").Rows(1)
.Delete Shift:=xlUp
End With
End If
End If
End If
End With
End Sub

Note that the Worksheet_Change event will not fire when a selection is
made from a validation dropdown in XL97 and MacXL (don't remember about
XL00). For compatibility with those versions, use the _Calculate event
and check your range for "Finalized".

Hi

[quoted text clipped - 19 lines]

Many thanks in advance...

--
Message posted via http://www.officekb.com