bonjour,
code adapté
Sub tirage(c)
Dim participants
If c <> 21 Then If Cells(c + 2, 7) = "" Then MsgBox "vous ne pouvez pas encore faire ce tirage": Exit Sub
dl = Cells(Rows.Count, 1).End(xlUp).Row
ReDim participants(1 To dl)
k = 0
For i = c To 3 Step -2
Cells(i, 7) = ""
Next i
For i = 2 To dl 'on prépare une liste de personnes à tirer au sort
For j = 21 To c Step -2
If Cells(i, 1) = Cells(j, 7) Then j = 0
Next j
If j > 0 Then 'personne non tirée précédemment
k = k + 1
participants(k) = Cells(i, 1)
End If
Next i
Cells(c, 7).Interior.Color = RGB(150, 200, 230)
Cells(c, 7).Font.Bold = False
Cells(c, 7).Font.Size = 12
For i = 1 To 100
q = Application.RandBetween(1, k)
Cells(c, 7) = participants(q)
For j = 1 To i * 50000: Next j
Next i
Cells(c, 7).Interior.Color = RGB(255, 217, 102)
Cells(c, 7).Font.Bold = True
Cells(c, 7).Font.Size = 28
End Sub
Sub tirage1()
tirage (3)
End Sub
Sub tirage2()
tirage (5)
End Sub
Sub tirage3()
tirage (7)
End Sub
Sub tirage4()
tirage (9)
End Sub
Sub tirage5()
tirage (11)
End Sub
Sub tirage6()
tirage (13)
End Sub
Sub tirage7()
tirage (15)
End Sub
Sub tirage8()
tirage (17)
End Sub
Sub tirage9()
tirage (19)
End Sub
Sub tirage10()
tirage (21)
End Sub