Bjr,
J'ai créé dans VBA, sur une feuille 2 worksheet et elles ne peuvent s'exécuter ensemble alors que séparement elles marchent trés bien. Je vous ai joint le fichier.
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A22:A25")) Is Nothing Then: Exit Sub 'Supprimer pour toute la page With Target
Select Case Target.Value
Case Is = "A"
.Font.ColorIndex = 3 'caractère en rouge
With Selection.Interior
.ColorIndex = 2 'fond blanc
.Pattern = xlSolid
End With
Case Is = "CEF"
.Font.ColorIndex = 7 'caractère en rose
With Selection.Interior
.ColorIndex = 2 'fond blanc
.Pattern = xlSolid
End With
Case Is = "CET"
.Font.ColorIndex = 2 'caractère en blanc
With Selection.Interior
.ColorIndex = 3 'fond rouge
.Pattern = xlSolid
End With
Case Is = "CMAT"
.Font.ColorIndex = 46 'caractère en orange
With Selection.Interior
.ColorIndex = 2 'fond blanc
.Pattern = xlSolid
End With
Case Is = "CPAT"
.Font.ColorIndex = 46 'caractère en orange
With Selection.Interior
.ColorIndex = 2 'fond blanc
.Pattern = xlSolid
End With
Case Is = "CP"
.Font.ColorIndex = 5 'caractère en bleu
With Selection.Interior
.ColorIndex = 2 'fond blanc
.Pattern = xlSolid
End With
Case Is = "CP½A"
.Font.ColorIndex = 5 'caractère en bleu
With Selection.Interior
.ColorIndex = 2 'fond blanc
.Pattern = xlSolid
End With
Case Is = "CP½M"
.Font.ColorIndex = 5 'caractère en bleu
With Selection.Interior
.ColorIndex = 2 'fond blanc
.Pattern = xlSolid
End With
Case Is = "CPE"
.Font.ColorIndex = 9 'caractère en marron
With Selection.Interior
.ColorIndex = 2 'fond blanc
.Pattern = xlSolid
End With
Case Is = "CSS"
.Font.ColorIndex = 8 'caractère en turquoise
With Selection.Interior
.ColorIndex = 2 'fond blanc
.Pattern = xlSolid
End With
Case Is = "EMAL"
.Font.ColorIndex = 1 'caractère en noir
With Selection.Interior
.ColorIndex = 7 'fond rose
.Pattern = xlSolid
End With
Case Is = "EMAL½A"
.Font.ColorIndex = 1 'caractère en noir
With Selection.Interior
.ColorIndex = 7 'fond rose
.Pattern = xlSolid
End With
Case Is = "EMAL½M"
.Font.ColorIndex = 1 'caractère en noir
With Selection.Interior
.ColorIndex = 7 'fond rose
.Pattern = xlSolid
End With
Case Is = "MAL"
.Font.ColorIndex = 1 'caractère en noir
With Selection.Interior
.ColorIndex = 2 'fond blanc
.Pattern = xlSolid
End With
Case Is = "MAL½A"
.Font.ColorIndex = 1 'caractère en noir
With Selection.Interior
.ColorIndex = 2 'fond blanc
.Pattern = xlSolid
End With
Case Is = "MAL½M"
.Font.ColorIndex = 1 'caractère en noir
With Selection.Interior
.ColorIndex = 2 'fond blanc
.Pattern = xlSolid
End With
Case Is = "RTT"
.Font.ColorIndex = 10 'caractère en vert
With Selection.Interior
.ColorIndex = 2 'fond blanc
.Pattern = xlSolid
End With
Case Is = "RTT½A"
.Font.ColorIndex = 10 'caractère en vert
With Selection.Interior
.ColorIndex = 2 'fond blanc
.Pattern = xlSolid
End With
Case Is = "RTT½M"
.Font.ColorIndex = 10 'caractère en vert
With Selection.Interior
.ColorIndex = 2 'fond blanc
.Pattern = xlSolid
End With
Case Is = "RTTE"
.Font.ColorIndex = 2 'caractère en blanc
With Selection.Interior
.ColorIndex = 10 'fond vert
.Pattern = xlSolid
End With
Case Is = "RTTE TRAV"
.Font.ColorIndex = 3 'caractère en rouge
With Selection.Interior
.ColorIndex = 10 'fond vert
.Pattern = xlSolid
End With
Case Else
.Font.ColorIndex = xlNone
.Interior.ColorIndex = xlNone
End Select
End With
End Sub
Je me demande si le texte en rouge ne provoque pas un bug sur la 2eme.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$C$22" Then If Not (IsEmpty(Target.Value)) Then Range("F22").Value = Now Else Range("F22").ClearContents
If Target.Address = "$C$23" Then If Not (IsEmpty(Target.Value)) Then Range("F23").Value = Now Else Range("F23").ClearContents
If Target.Address = "$C$24" Then If Not (IsEmpty(Target.Value)) Then Range("F24").Value = Now Else Range("F24").ClearContents
If Target.Address = "$C$25" Then If Not (IsEmpty(Target.Value)) Then Range("F25").Value = Now Else Range("F25").ClearContents
End Sub
Cette 2eme procedure devrait mettre la date du jour en colonne F puis la convertir pour qu'elle ne change à une prochaine ouverture. Et elle amrche trés bien seule.
Comment transcrire ces procédures en une seule. Merci de votre aide !
Vérolyde
J'ai créé dans VBA, sur une feuille 2 worksheet et elles ne peuvent s'exécuter ensemble alors que séparement elles marchent trés bien. Je vous ai joint le fichier.
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A22:A25")) Is Nothing Then: Exit Sub 'Supprimer pour toute la page With Target
Select Case Target.Value
Case Is = "A"
.Font.ColorIndex = 3 'caractère en rouge
With Selection.Interior
.ColorIndex = 2 'fond blanc
.Pattern = xlSolid
End With
Case Is = "CEF"
.Font.ColorIndex = 7 'caractère en rose
With Selection.Interior
.ColorIndex = 2 'fond blanc
.Pattern = xlSolid
End With
Case Is = "CET"
.Font.ColorIndex = 2 'caractère en blanc
With Selection.Interior
.ColorIndex = 3 'fond rouge
.Pattern = xlSolid
End With
Case Is = "CMAT"
.Font.ColorIndex = 46 'caractère en orange
With Selection.Interior
.ColorIndex = 2 'fond blanc
.Pattern = xlSolid
End With
Case Is = "CPAT"
.Font.ColorIndex = 46 'caractère en orange
With Selection.Interior
.ColorIndex = 2 'fond blanc
.Pattern = xlSolid
End With
Case Is = "CP"
.Font.ColorIndex = 5 'caractère en bleu
With Selection.Interior
.ColorIndex = 2 'fond blanc
.Pattern = xlSolid
End With
Case Is = "CP½A"
.Font.ColorIndex = 5 'caractère en bleu
With Selection.Interior
.ColorIndex = 2 'fond blanc
.Pattern = xlSolid
End With
Case Is = "CP½M"
.Font.ColorIndex = 5 'caractère en bleu
With Selection.Interior
.ColorIndex = 2 'fond blanc
.Pattern = xlSolid
End With
Case Is = "CPE"
.Font.ColorIndex = 9 'caractère en marron
With Selection.Interior
.ColorIndex = 2 'fond blanc
.Pattern = xlSolid
End With
Case Is = "CSS"
.Font.ColorIndex = 8 'caractère en turquoise
With Selection.Interior
.ColorIndex = 2 'fond blanc
.Pattern = xlSolid
End With
Case Is = "EMAL"
.Font.ColorIndex = 1 'caractère en noir
With Selection.Interior
.ColorIndex = 7 'fond rose
.Pattern = xlSolid
End With
Case Is = "EMAL½A"
.Font.ColorIndex = 1 'caractère en noir
With Selection.Interior
.ColorIndex = 7 'fond rose
.Pattern = xlSolid
End With
Case Is = "EMAL½M"
.Font.ColorIndex = 1 'caractère en noir
With Selection.Interior
.ColorIndex = 7 'fond rose
.Pattern = xlSolid
End With
Case Is = "MAL"
.Font.ColorIndex = 1 'caractère en noir
With Selection.Interior
.ColorIndex = 2 'fond blanc
.Pattern = xlSolid
End With
Case Is = "MAL½A"
.Font.ColorIndex = 1 'caractère en noir
With Selection.Interior
.ColorIndex = 2 'fond blanc
.Pattern = xlSolid
End With
Case Is = "MAL½M"
.Font.ColorIndex = 1 'caractère en noir
With Selection.Interior
.ColorIndex = 2 'fond blanc
.Pattern = xlSolid
End With
Case Is = "RTT"
.Font.ColorIndex = 10 'caractère en vert
With Selection.Interior
.ColorIndex = 2 'fond blanc
.Pattern = xlSolid
End With
Case Is = "RTT½A"
.Font.ColorIndex = 10 'caractère en vert
With Selection.Interior
.ColorIndex = 2 'fond blanc
.Pattern = xlSolid
End With
Case Is = "RTT½M"
.Font.ColorIndex = 10 'caractère en vert
With Selection.Interior
.ColorIndex = 2 'fond blanc
.Pattern = xlSolid
End With
Case Is = "RTTE"
.Font.ColorIndex = 2 'caractère en blanc
With Selection.Interior
.ColorIndex = 10 'fond vert
.Pattern = xlSolid
End With
Case Is = "RTTE TRAV"
.Font.ColorIndex = 3 'caractère en rouge
With Selection.Interior
.ColorIndex = 10 'fond vert
.Pattern = xlSolid
End With
Case Else
.Font.ColorIndex = xlNone
.Interior.ColorIndex = xlNone
End Select
End With
End Sub
Je me demande si le texte en rouge ne provoque pas un bug sur la 2eme.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$C$22" Then If Not (IsEmpty(Target.Value)) Then Range("F22").Value = Now Else Range("F22").ClearContents
If Target.Address = "$C$23" Then If Not (IsEmpty(Target.Value)) Then Range("F23").Value = Now Else Range("F23").ClearContents
If Target.Address = "$C$24" Then If Not (IsEmpty(Target.Value)) Then Range("F24").Value = Now Else Range("F24").ClearContents
If Target.Address = "$C$25" Then If Not (IsEmpty(Target.Value)) Then Range("F25").Value = Now Else Range("F25").ClearContents
End Sub
Cette 2eme procedure devrait mettre la date du jour en colonne F puis la convertir pour qu'elle ne change à une prochaine ouverture. Et elle amrche trés bien seule.
Comment transcrire ces procédures en une seule. Merci de votre aide !
Vérolyde