Hi Jan,

Am Fri, 16 May 2014 21:09:03 +0100 schrieb IanKR:


I can't see an easy way to do this, without storing whats already in the
cell, before deletion. Like if the number 2 is deleted, then M20 has to be
deleted too. But when the cell content is deleted, I won't know, what was in
it before.

try:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("D4:D9")) Is Nothing _
Then Exit Sub

Dim myStr As String
Dim myArr As Variant
Dim i As Long

myStr = "M13,M20,H9,H17,H25,H33"
myArr = Split(myStr, ",")

Select Case Target.Value
Case 1 To 6
Range(myArr(Target.Value - 1)) = Target.Offset(, -1)
End Select

For i = 1 To 6
If WorksheetFunction.CountIf(Range("D4:D9"), i) = 0 Then
Range(myArr(i - 1)).ClearContents
End If
Next
End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional