Private Sub Worksheet_Change(ByVal Target As Range)
Set Target = Intersect(Target, [C2])
If Target Is Nothing Then Exit Sub
Dim lig As Variant, col As Integer
With Sheets("Historical Market Values") 'With Sheet4 'CodeName
lig = Application.Match(Target, .[A:A], 0)
col = .Cells(1, .Columns.count).End(xlToLeft).Column 'dernière colonne
[A4].Resize(col) = Application.Transpose(.[A1].Resize(, col))
If IsNumeric(lig) Then _
[B5].Resize(col) = Application.Transpose(.Cells(lig, 2).Resize(, col))
[A4:B4].Offset(IIf(IsNumeric(lig), col, 0)).Resize(20000).ClearContents
End With
End Sub