Tirage au sort conditionnel

Bonjour

en cherchent j'ai trouve une macro Tirage au sort qui fais en partie ce que je souhaite pour mon projet

_ j'ai une liste de chiffre chacun correspond a un collaborateur

_ Dans les cellule BA,BC,BE,BG,BI,BK,BN

j'aimerai faire dans un ligne sur trois tout les jours de l’année un tirage au sort aléatoire 7 chiffre dans la liste des collaborateurs qui n'on pas de motif d'absence CP,CN,CD,MA,MOD, CSS et le chiffre du collaborateur d’astreinte qui se trouve dans la plage ( H7:AX7)

je vous joint un fichier échantillon

Merci d'avance

Sub Tirage_au_sort()
Dim i As Integer, DerLig As Integer

Application.ScreenUpdating = False

With Sheets("Feuil2")
    DerLig = .Range("A1048576").End(xlUp).Row
    .Range("A14:A23" & DerLig).Copy Destination:=.Range("BR14")
    For i = 14 To DerLig
        .Range("BQ" & i) = Rnd
    Next
    .Range("BQ14:BR" & DerLig).Sort Key1:=.Range("BQ14"), Order1:=xlAscending, Header:=xlNo

    Range("BA4") = .Range("BR14")
    Range("BC4") = .Range("BR15")
    Range("BE4") = .Range("BR16")
    Range("BG4") = .Range("BR17")
    Range("BI4") = .Range("BR18")
    Range("BK4") = .Range("BR19")
    Range("BM4") = .Range("BR20")
    .Range("BQ14:BR1048576").ClearContents
End With

End Sub