Rick,

Good point, I assumed (never safe to do) that the OP used cut & paste
because he didn't know any other methods.

Mike

"Rick Rothstein" wrote:

One possible problem for you with Mike's solution... it doesn't duplicate
the Cut/Paste operation as Excel performs it. If the Target cell had
formatting, Mike's code leaves that formatting in place whereas Cut would
remove it. Also, Paste copies the formatting from the Cut cell over into the
new location whereas Mike's code doesn't.

The most direct route to do what you want is based on the code that Don
posted...

......
If Left(Target.Value, 1) = "6" Then
Target.Cut Target.Offset(, 1)
End If
......

but if you want to do it in two steps as Mike proposed, these steps would do
what Cut/Paste does...

......
If LResult = "6" Then
Target.Copy Target.Offset(0, 1)
Target.Clear
End If
......

--
Rick (MVP - Excel)


"JSnow" wrote in message
...
BRILLIANT! Thanks so much.

"Mike H" wrote:

Try it this way

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Whoops
If Target.Column = 3 And Target.Row 1 Then 'ignore row 1
Application.EnableEvents = False
Dim LResult As String
If Left(Target.Value, 1) = "6" Then
Target.Offset(, 1).Value = Target.Value
Target.Value = ""
End If
End If
Whoops:
Application.EnableEvents = True
End Sub

Mike

"JSnow" wrote:

I'm using Excel 2003 and want to move data from a cell in column C to
column
D (same row) if the data starts with a six. Here's my code thus far:

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo Whoops
If Target.Column = 3 Then 'column C
If Target.Row 1 Then 'ignore row 1
Dim LResult As String
LResult = Left(Target.Value, 1)
If LResult = "6" Then
Selection.Cut
ActiveCell.Offset(0, 1).Select
ActiveSheet.Paste
End If
End If
End If

Whoops:
Application.EnableEvents = True
End Sub

Nothing happens. The sheet just sits there and mocks me.