UserForm couleur de fond
M
Bonjour
J'ai créé un userform qui me lance automatiquement une macro via un bouton, jusqu'à la tout va bien. Cette macro étant relié à un tableau dans lequel elle saisit les données, j'aimerai trouver une formule pour dire au userform de NE PAS SELECTIONNER les cellules qui ont un fond coloré car ce sont des données qu'on ne veut pas voir apparaître mais pour lesquelles on veut garder une trace. C'est pour cela que j'aimerai colorier la cellule et dire a mon userform de ne pas la prendre en compte et passer à la donnée suivante.
Voici ma macro :
Private Sub CommandButton1_Click()
Unload UserForm14
Sheets("autorisation").Cells(1, 2) = ""
Sheets("autorisation").Select
'
Sheets("Feuil3").Cells(1, 2) = ""
For Lig = 8 To 111
If Sheets("autorisation").Cells(1, 2) = "" Or Sheets("formations").Cells(Lig, 1) = Sheets("autorisation").Cells(1, 2) Then
If Sheets("formations").Cells(Lig, 50) <> "X" Then
Range("E4:G24").Select
Selection.ClearContents
Range("E27:G47").Select
Selection.ClearContents
Sheets("autorisation").Cells(19, 2) = Sheets("formations").Cells(Lig, 1)
Sheets("autorisation").Cells(19, 3) = Sheets("formations").Cells(Lig, 2)
Sheets("autorisation").Cells(21, 3) = Sheets("formations").Cells(Lig, 3)
Sheets("autorisation").Cells(23, 3) = Sheets("formations").Cells(Lig, 4)
Sheets("autorisation").Cells(42, 2) = Sheets("formations").Cells(Lig, 1)
Sheets("autorisation").Cells(42, 3) = Sheets("formations").Cells(Lig, 2)
Sheets("autorisation").Cells(44, 3) = Sheets("formations").Cells(Lig, 3)
Sheets("autorisation").Cells(46, 3) = Sheets("formations").Cells(Lig, 4)
lig1 = 3
lig24 = 27
If Sheets("formations").Cells(Lig, 11) > " " Then
lig1 = lig1 + 1
Sheets("autorisation").Cells(lig1, 5) = "CACES R389 CAT 3"
Sheets("autorisation").Cells(lig1, 6) = Sheets("formations").Cells(Lig, 11)
Sheets("autorisation").Cells(lig1, 7) = Sheets("formations").Cells(Lig, 12)
End If
If Sheets("formations").Cells(Lig, 11) > " " Then
lig24 = lig24 + 1
Sheets("autorisation").Cells(lig24, 5) = "CACES R389 CAT 3"
Sheets("autorisation").Cells(lig24, 6) = Sheets("formations").Cells(Lig, 11)
Sheets("autorisation").Cells(lig24, 7) = Sheets("formations").Cells(Lig, 12)
End If
If Sheets("formations").Cells(Lig, 13) > " " Then
lig1 = lig1 + 1
Sheets("autorisation").Cells(lig1, 5) = "CACES R389 CAT 4"
Sheets("autorisation").Cells(lig1, 6) = Sheets("formations").Cells(Lig, 13)
Sheets("autorisation").Cells(lig1, 7) = Sheets("formations").Cells(Lig, 14)
End If
If Sheets("formations").Cells(Lig, 13) > " " Then
lig24 = lig24 + 1
Sheets("autorisation").Cells(lig24, 5) = "CACES R389 CAT 4"
Sheets("autorisation").Cells(lig24, 6) = Sheets("formations").Cells(Lig, 13)
Sheets("autorisation").Cells(lig24, 7) = Sheets("formations").Cells(Lig, 14)
End If
If Sheets("formations").Cells(Lig, 16) > " " Then
lig1 = lig1 + 1
Sheets("autorisation").Cells(lig1, 5) = "CACES R372 CAT 9"
Sheets("autorisation").Cells(lig1, 6) = Sheets("formations").Cells(Lig, 16)
Sheets("autorisation").Cells(lig1, 7) = Sheets("formations").Cells(Lig, 17)
End If
If Sheets("formations").Cells(Lig, 16) > " " Then
lig24 = lig24 + 1
Sheets("autorisation").Cells(lig24, 5) = "CACES R372 CAT 9"
Sheets("autorisation").Cells(lig24, 6) = Sheets("formations").Cells(Lig, 16)
Sheets("autorisation").Cells(lig24, 7) = Sheets("formations").Cells(Lig, 17)
End If
If Sheets("formations").Cells(Lig, 19) > " " Then
lig1 = lig1 + 1
Sheets("autorisation").Cells(lig1, 5) = "CACES R386 CAT 1B"
Sheets("autorisation").Cells(lig1, 6) = Sheets("formations").Cells(Lig, 19)
Sheets("autorisation").Cells(lig1, 7) = Sheets("formations").Cells(Lig, 20)
End If
If Sheets("formations").Cells(Lig, 19) > " " Then
lig24 = lig24 + 1
Sheets("autorisation").Cells(lig24, 5) = "CACES R386 CAT 1B"
Sheets("autorisation").Cells(lig24, 6) = Sheets("formations").Cells(Lig, 19)
Sheets("autorisation").Cells(lig24, 7) = Sheets("formations").Cells(Lig, 20)
End If
If Sheets("formations").Cells(Lig, 19) > " " Then
lig1 = lig1 + 1
Sheets("autorisation").Cells(lig1, 5) = "CACES R386 CAT 3B"
Sheets("autorisation").Cells(lig1, 6) = Sheets("formations").Cells(Lig, 19)
Sheets("autorisation").Cells(lig1, 7) = Sheets("formations").Cells(Lig, 20)
End If
If Sheets("formations").Cells(Lig, 19) > " " Then
lig24 = lig24 + 1
Sheets("autorisation").Cells(lig24, 5) = "CACES R386 CAT 3B"
Sheets("autorisation").Cells(lig24, 6) = Sheets("formations").Cells(Lig, 19)
Sheets("autorisation").Cells(lig24, 7) = Sheets("formations").Cells(Lig, 20)
End If
If Sheets("formations").Cells(Lig, 24) > " " Then
lig1 = lig1 + 1
Sheets("autorisation").Cells(lig1, 5) = "CACES R377 GME"
Sheets("autorisation").Cells(lig1, 6) = Sheets("formations").Cells(Lig, 24)
Sheets("autorisation").Cells(lig1, 7) = Sheets("formations").Cells(Lig, 25)
End If
If Sheets("formations").Cells(Lig, 24) > " " Then
lig24 = lig24 + 1
Sheets("autorisation").Cells(lig24, 5) = "CACES R377 GME"
Sheets("autorisation").Cells(lig24, 6) = Sheets("formations").Cells(Lig, 24)
Sheets("autorisation").Cells(lig24, 7) = Sheets("formations").Cells(Lig, 25)
End If
If Sheets("formations").Cells(Lig, 27) > " " Then
lig1 = lig1 + 1
Sheets("autorisation").Cells(lig1, 5) = "PONT ROULANT R484 CAT1"
Sheets("autorisation").Cells(lig1, 6) = Sheets("formations").Cells(Lig, 27)
Sheets("autorisation").Cells(lig1, 7) = Sheets("formations").Cells(Lig, 28)
End If
If Sheets("formations").Cells(Lig, 27) > " " Then
lig24 = lig24 + 1
Sheets("autorisation").Cells(lig24, 5) = "PONT ROULANT R484 CAT1"
Sheets("autorisation").Cells(lig24, 6) = Sheets("formations").Cells(Lig, 27)
Sheets("autorisation").Cells(lig24, 7) = Sheets("formations").Cells(Lig, 28)
End If
End If
Application.Wait (Now + TimeValue("0:00:04"))
Sheets("autorisation").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
IgnorePrintAreas:=False
End If
End If
Next Lig
Sheets("autorisation").Select
Range("A3").Select
End SubBonjour,
Une piste, à adapter :
For i = x To y
If Sheets("lafeuille").Cells(i, 1).Interior.Color <> 1677215 Then Next i
'Ton code
Next i