Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, [B1]) Is Nothing Then
Dim Sh As Worksheet, Rng As Range, sRng As Range, Cls As Range
Dim Col As Byte, Rws As Long
Set Sh = Sheet1: Set Rng = Sh.Rows("5:5")
Col = Rng.Find([B1].Value, , xlFormulas, xlWhole).Column
Rws = Sh.Range(Sh.[a7], Sh.[a7].End(xlDown)).Rows.Count
[C4].Resize(2 * Rws, 7).Clear
[B4].Resize(Rws).Font.ColorIndex = 2
For Each Cls In Sh.Cells(7, Col).Resize(Rws)
If Cls.Value <> "" Then
Set sRng = Sh.Cells(Cls.Row, "A")
With [c65500].End(xlUp).Offset(1)
.Value = sRng.Value
.Offset(, 1).Value = "F" & sRng.Offset(, 1).Value
.Offset(, 2).Resize(, 4).Value = sRng.Offset(, 2).Resize(, 4).Value
.Offset(1).Value = sRng.Value & "-PKG"
.Offset(1).HorizontalAlignment = xlRight
.Offset(, 1).Value = "P" & sRng.Offset(, 1).Value
.Offset(, 2).Resize(, 4).Value = Cls.Offset(, 1).Resize(, 4).Value
.Resize(2, 7).Interior.ColorIndex = 35 + Cls.Row Mod 7
.Offset(, -1).Resize(2).Font.ColorIndex = 0
End With
End If
Next Cls
End If
Exit Sub: End Sub