Private Sub ComboBox1_Change()
Dim cel As Range, Plage As Range, l As Long, derl As Long, c As Byte
c = ComboBox1.ListIndex + 1
Range("B5:B15").ClearContents
[A5:A15].Sort key1:=[A5], order1:=xlAscending
derl = Cells(65536, c).End(xlUp).Row
Set Plage = Range(Cells(24, c), Cells(derl, c)) '.Address
Plage.Sort key1:=Cells(24, c), order1:=xlAscending
For l = 24 To derl
For Each cel In Range("A5:A15")
If UCase(cel) = UCase(Cells(l, c)) And cel.Offset(, 1) = "" Then
cel.Offset(, 1) = "oui"
Exit For
End If
Next cel
Next l
For Each cel In Range("B5:B15")
If cel = "" Then cel = "non"
Next cel
End Sub