Right click on the Sheet Tab and select View Code. Paste in code like this:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ErrHandler
If Target(1).Column = 1 And Target(1).Column <= 8 Then
If Not IsEmpty(Target(1)) Then
Set rng = Cells(Target(1).Row, 1).Resize(1, 8)
Application.EnableEvents = False
For Each cell In rng
If Not IsEmpty(cell) Then
If Target(1).Address < cell.Address Then
cell.ClearContents
End If
End If
Next
Cells(Target(1).Row, 9).Value = Target(1).Value
End If
End If
ErrHandler:
Application.EnableEvents = True

End Sub

--
Regards,
Tom Ogilvy

"Duncan_J" wrote in message
...
Hi,
I use to have the code for this..
I have 8 cells on a row and only one of the cells on that row may contain
data. I want the rows to right on the ninth coulmn whatever is in the
eight
cells to the left

Example:
Blank Blank Blank Yellow Blank Blank blank blank Yellow
blank Red blank Blank Blank blank blank blank Red

Thanks for the help