Hi

Sorry i misunderstood what you were trying to do. I have modified the
code and pasted below, you were right about using target.count but
since we have already checked it we can simply add an "else" statement
to the existing "if" statement. Then all i did was replace the column
letters in the range to show J instead of C and then clear that
range. This seems to work fine unless you filldown or paste a single
value into the full highlighted range then the J column will be
cleared i am thinking you could get round this by testing the value of
the first cell in the range for "" or something.

Option Explicit
Dim SwitchColumn As String

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Count = 1 Then

If Target.Column = 3 And Target.Row < 1 Then

Cells(Target.Row, 10).Value = "'" & Mid(Target.Value, 7, 3)

End If

Else

If Target.Column = 3 And Target.Row < 1 Then

SwitchColumn = Replace(Target.Address, "C", "J")

Range(SwitchColumn).Clear

End If

End If

Let me know how you get on

Steve