Hi again,

Am Sat, 7 Sep 2013 11:03:04 +0200 schrieb Claus Busch:

you can do that also a bit easier (enlarge the code - I have only three
items in it):

or:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address < "$H$1" Then Exit Sub

Dim c As Range
Dim myOffset As Integer
Dim rngBig As Range

Select Case Target.Value
Case "Swine"
myOffset = 4
Case "Diary"
myOffset = 3
Case "Beef"
myOffset = 2
End Select

With Range(Target & "x").SpecialCells(xlCellTypeConstants)
Set rngBig = Union(Range(Target & "x") _
.SpecialCells(xlCellTypeConstants), _
.Offset(columnOffset:=myOffset), _
.Offset(columnOffset:=myOffset + 1))
rngBig.Copy Range("I50").End(xlUp).Offset(1, 0)
End With


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2