Bonjour,
Une autre proposition sans formule.
Cdlt.
Public Sub DEMO()
Dim v
Dim rng As Range, rCell As Range
Dim cn As Long, rw As Long
v = Cells(18, 3).Value
Select Case Cells(18, 5).Value
Case "bleu": cn = 2
Case "vert": cn = 4
Case "rouge": cn = 3
End Select
rw = 13
Set rng = Worksheets("Feuil1").UsedRange.Columns(1)
Set rCell = rng.Find(what:=v, LookIn:=xlValues, lookat:=xlWhole)
If rCell Is Nothing Then Exit Sub
Cells(19, 5).Resize(rw).Value = rCell.Offset(1, cn).Resize(rw).Value
Set rCell = Nothing: Set rng = Nothing
End Sub