XL 2010 Tableau de corvée.

=SOMMEPROD((JOURSEM($B$9:$B$38;2)<5)*($C$9:$C$38=$F10))

Sub Tirages()
Dim nom, nnom%, h%, corvee As Range, suppleant As Range
Dim ecart As Range, i%, r1%, r2%
nom = [C2:O2] 'à adapter
nnom = UBound(nom, 2)
h = Day(Application.EoMonth([B9], 0))
Set corvee = [C9].Resize(h)
Set suppleant = [D9].Resize(h)
Set ecart = [J43]
Application.ScreenUpdating = False
Randomize
corvee.ClearContents 'RAZ
suppleant.ClearContents 'RAZ
For i = 1 To h
    Do
        r1 = 1 + Int(Rnd * nnom)
        corvee(i) = nom(1, r1)
    Loop While ecart > 2 Or corvee(i) = corvee(i - 1) Or corvee(i) = suppleant(i - 1)
    Do
        r2 = 1 + Int(Rnd * nnom)
        suppleant(i) = nom(1, r2)
    Loop While r1 = r2 Or ecart > 2 Or suppleant(i) = corvee(i - 1)
Next i
End Sub