Changer la couleur d'un onglet EXCEL selon conditions

Résolu/Fermé
Signaler
Messages postés
28
Date d'inscription
lundi 24 juin 2013
Statut
Membre
Dernière intervention
30 août 2013
-
Messages postés
28
Date d'inscription
lundi 24 juin 2013
Statut
Membre
Dernière intervention
30 août 2013
-
Bonjour,

Je remplie via une formule plusieurs cellules par page. Si toutes ces cellules sont "valide" alors la couleur de l'onglet doit être verte.
Si une seule est "non valide" alors l'onglet doit être de couleur rouge
Si certaine ne sont pas rempli, l'onglet ne change pas de couleur.

Je souhaite faire cela via VBA.
M'avez vous un code simple pour cela ? Je suis un novice.
Je vous remercie

10 réponses

Messages postés
1953
Date d'inscription
lundi 3 mai 2010
Statut
Membre
Dernière intervention
12 août 2021
151
Bon, t'as essayé un truc mais tu pars de loin, j'ai l'impression...
Colle ceci dans "ThisWorkbook" dans l'éditeur VBA :
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    If (ActiveSheet.Cells(9, 2) = "Valide" And ActiveSheet.Cells(12, 2) = "Valide") Then
        ActiveSheet.Tab.ColorIndex = 10 'vert
    ElseIf (ActiveSheet.Cells(9, 2) = "Non Valide" Or ActiveSheet.Cells(12, 2) = "Non Valide") Then
        ActiveSheet.Tab.ColorIndex = 3 'rouge
    Else
        ActiveSheet.Tab.ColorIndex = 1 'noir
    End If
End Sub

Une macro est soit exécutée à la demande (Alt+F8 ou bouton), soit s'exécute automatiquement. Pour ce deuxième moyen, il y a des noms de procédures réservés correspondant à l'action qui la déclenche ; dans ce cas, tout changement de sélection dans une feuille.
On peut adapter le code pour que les onglets se mettent tous dans la bonne couleur lors de la sauvegarde uniquement...

PS : Salutations à pijaku
1
Messages postés
28
Date d'inscription
lundi 24 juin 2013
Statut
Membre
Dernière intervention
30 août 2013
5
Ok. Oui je pars de loin...
Je l'ai collé dans ThisWorkbook.

Pour la faire fonctionner, j'ai essayé d'appuyer sur le "Play" mais une fenêtre d'ouvre "Macro" ou je dois rentrer un nom...
0
Messages postés
28
Date d'inscription
lundi 24 juin 2013
Statut
Membre
Dernière intervention
30 août 2013
5
Je me complique la vie. Pas besoin de mettre play. Cela fonctionne... :)
0
Messages postés
1953
Date d'inscription
lundi 3 mai 2010
Statut
Membre
Dernière intervention
12 août 2021
151
Résolu ?
0
Messages postés
28
Date d'inscription
lundi 24 juin 2013
Statut
Membre
Dernière intervention
30 août 2013
5
Presque, :) voir message du dessous si vous avez encore 2 minutes pour moi.
0
Messages postés
28
Date d'inscription
lundi 24 juin 2013
Statut
Membre
Dernière intervention
30 août 2013
5
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
 If (ActiveSheet.Cells(9, 2) = "VALIDE" And ActiveSheet.Cells(12, 2) = "VALIDE" And ActiveSheet.Cells(15, 2) = "VALIDE" And ActiveSheet.Cells(18, 2) = "VALIDE" And ActiveSheet.Cells(21, 2) = "VALIDE" And ActiveSheet.Cells(24, 2) = "VALIDE" And ActiveSheet.Cells(27, 2) = "VALIDE" And ActiveSheet.Cells(30, 2) = "VALIDE" And ActiveSheet.Cells(33, 2) = "VALIDE" And ActiveSheet.Cells(36, 2) = "VALIDE" And ActiveSheet.Cells(39, 2) = "VALIDE" And ActiveSheet.Cells(42, 2) = "VALIDE" And ActiveSheet.Cells(45, 2) = "VALIDE" And ActiveSheet.Cells(48, 2) = "VALIDE") Then
        ActiveSheet.Tab.ColorIndex = 10 'vert
    ElseIf (ActiveSheet.Cells(9, 2) = "NON VALIDE" Or ActiveSheet.Cells(12, 2) = "NON VALIDE" Or ActiveSheet.Cells(15, 2) = "NON VALIDE" Or ActiveSheet.Cells(18, 2) = "NON VALIDE" Or ActiveSheet.Cells(21, 2) = "NON VALIDE" Or ActiveSheet.Cells(24, 2) = "NON VALIDE" Or ActiveSheet.Cells(27, 2) = "NON VALIDE" Or ActiveSheet.Cells(30, 2) = "NON VALIDE" Or ActiveSheet.Cells(33, 2) = "NON VALIDE" Or ActiveSheet.Cells(36, 2) = "NON VALIDE" Or ActiveSheet.Cells(39, 2) = "NON VALIDE" Or ActiveSheet.Cells(42, 2) = "NON VALIDE" Or ActiveSheet.Cells(45, 2) = "NON VALIDE" Or ActiveSheet.Cells(48, 2) = "NON VALIDE") Then
        ActiveSheet.Tab.ColorIndex = 3 'rouge
    Else
        ActiveSheet.Tab.ColorIndex = 1 'noir
    End If
End Sub


Mon résultat pour le moment.
1
Messages postés
1953
Date d'inscription
lundi 3 mai 2010
Statut
Membre
Dernière intervention
12 août 2021
151
Tu arriverais à écrire un algorithme en français courant ? Après, on le traduit en VBA.
0
Messages postés
28
Date d'inscription
lundi 24 juin 2013
Statut
Membre
Dernière intervention
30 août 2013
5
Non je suis désolé, j'en serais incapable... L'informatique n'est pas mon domaine de compétences et n'ai jamais fait d'algorithme.
Cependant ce que vous m'avez donné est déjà pas mal.

Il faudrait que je l'adapte manuellement à chaque page EXCEL. Je doute que ce soit la meilleure façon mais si elle fonctionne, ça ira. Même si cela demande un peu plus de travail.

Au lieu de l'inscrire dans le WORKSHEET, je peux l'inscrire dans chaque page en adaptant ?
0
Messages postés
1953
Date d'inscription
lundi 3 mai 2010
Statut
Membre
Dernière intervention
12 août 2021
151
Oui, mais si chaque onglet a la même structure, autant ne pas se fatiguer à copier et adapter la macro.
0
Messages postés
28
Date d'inscription
lundi 24 juin 2013
Statut
Membre
Dernière intervention
30 août 2013
5
Certains onglets sont plus court. Ils sont tous formés pareils (B9, B12...) mais certains s'arrêtent plus tôt, par exemple B21
0
Messages postés
12257
Date d'inscription
jeudi 15 mai 2008
Statut
Modérateur
Dernière intervention
3 septembre 2021
2 675
Bonjour,

Une syntaxe, peut être plus facile à adapter :

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Dim RngPlageVal As Range, RngCel As Range
Dim Valid As Integer, NonValid As Integer

'Ce Range peut être facilement adapté :
Set RngPlageVal = Range("B9,B12,B15,B18, B21,B24,B27,B30,B33,B36,B39,B42,B45,B48")

'On va boucler sur toutes les cellules du Range et compter
'les "valide" et "non valide". 
For Each RngCel In RngPlageVal
    If RngCel.Value = "VALIDE" Then Valid = Valid + 1
    If RngCel.Value = "NON VALIDE" Then NonValid = NonValid + 1
Next
'si le nombre de cellules valides = Nombre de cellule de notre plage
'Autrement dit : Si toutes les cellules de notre plage sont "valides"
 If Valid = RngPlageVal.Cells.Count Then  
    'alors vert
    ActiveSheet.Tab.ColorIndex = 10 'vert
'Si Une et Une seule cellule est Non Valide Alors
ElseIf NonValid = 1 Then
    ActiveSheet.Tab.ColorIndex = 3 'rouge
'Dans tous les autres cas : 
Else
    ActiveSheet.Tab.ColorIndex = 1 'noir
End If
End Sub 


Cordialement,
Franck
1
Messages postés
1953
Date d'inscription
lundi 3 mai 2010
Statut
Membre
Dernière intervention
12 août 2021
151
Bonjour pijaku / Franck,

Je pense que c'est bien vu de définir la plage de cette façon.
Toutefois, je crois que Tom souhaite tolérer que les cellules soient vides (et donc avoir un onglet vert). Un booléen pour voir si on rencontre autre chose que "VALIDE" ou une cellule vide me semble plus approprié.

Tom188, peux-tu confirmer le besoin souligné ?
0
Messages postés
28
Date d'inscription
lundi 24 juin 2013
Statut
Membre
Dernière intervention
30 août 2013
5
Bonjour,
Si la formation n'est pas faite, par exemple en B21 et que la cellule est vide. L'onglet doit être noir.
Vert si tout est VALIDE
Rouge si un seul NON VALIDE (même si des cellules sont vides comme B21)
Noir si incomplet.

J'espère être clair, je suis conscient que mes explications pour des experts doivent pas être évidentes...
0
Messages postés
28
Date d'inscription
lundi 24 juin 2013
Statut
Membre
Dernière intervention
30 août 2013
5
Cela fonctionne également.

Je n'arrive cependant pas à la faire fonctionner pour les onglets n'ayant par exemple que B9,B12,B15,B18, B21,B24,B27,B30,B33,B36

Car les B39,B42,B45,B48 n'étant pas remplis, l'onglet reste au noir.

Dois je copier la formule dans les code de chaque page et l'adapter ?
Je vous remercie.

J'ai déjà énormément avancé.
1
Messages postés
1953
Date d'inscription
lundi 3 mai 2010
Statut
Membre
Dernière intervention
12 août 2021
151
Tu peux copier, mais idéalement, il faudrait distinguer s'il est normal ou pas que B39,B42,B45,B48 soient vides.
Par exemple en lisant ton fichier, comment vois-je que Gérard DUPONT n'a pas passé la formation B39 mais que c'est normal ? Alors que pour Michel DURAND, B39 est aussi vide mais il devrait passer la formation..
0
Messages postés
28
Date d'inscription
lundi 24 juin 2013
Statut
Membre
Dernière intervention
30 août 2013
5
C'est un peu plus vicieux. C'est pas qu'il soit normal ou anormal, c'est que leur poste ne les amènera jamais à passer ces formations. Donc la tableau s'arrête plus tôt. Les mentions ne sont pas les mêmes selon les agents.

Pour compléter, je dirais que l'onglet doit être VERT pour toutes les formations qu'un agents lambda doit VALIDER. Un agent peut avoir un tableau allant jusque B21, un autre jusque B48 etc...

La longueur des tableaux n'est pas forcément la même selon les agents. Mais c'est toujours les mêmes cellules qui sont remplies (la première est forcément B9, la seconde 3 cellules plus bas...) seulement il ne peut y en avoir que 4 ou 5 ou...

J'essaie d'être au maximum clair ;)
0
Messages postés
1953
Date d'inscription
lundi 3 mai 2010
Statut
Membre
Dernière intervention
12 août 2021
151
ça j'avais à peu près compris, ce qui serait bien pour produire une macro unique, c'est un truc qui saute aux yeux indiquant que l'agent A n'a pas besoin d'avoir plus de formation que jusque B21. Par exemple, en B24, indiquer : "pas d'autre formation nécessaire".
0
Messages postés
28
Date d'inscription
lundi 24 juin 2013
Statut
Membre
Dernière intervention
30 août 2013
5
Je pourrais indiquer un STOP oui.
0
Messages postés
1953
Date d'inscription
lundi 3 mai 2010
Statut
Membre
Dernière intervention
12 août 2021
151
Si tu écris "STOP", tu peux essayer le code suivant :
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    Dim Valid As Integer, NonValid As Integer, couleur_onglet As Integer, ligne As Integer
    
    couleur_onglet = 10 'vert par défaut
    NonValid = 0
    
    For ligne = 9 To 48 Step 3
        If (ActiveSheet.Cells(ligne, 2).Value <> "VALIDE" And ActiveSheet.Cells(ligne, 2).Value <> "NON VALIDE" And ActiveSheet.Cells(ligne, 2).Value <> "STOP") Then 'cellule vide ou de contenu anormal
            couleur_onglet = 1 'noir
            Exit For
        ElseIf ActiveSheet.Cells(ligne, 2).Value = "NON VALIDE" Then
            couleur_onglet = 3 'rouge
            NonValid = NonValid + 1
            If NonValid = 2 Then
                couleur_onglet = 1 'noir
                Exit For
            End If
        ElseIf ActiveSheet.Cells(ligne, 2).Value = "STOP" Then
            Exit For
        End If
    Next ligne
    
    ActiveSheet.Tab.ColorIndex = couleur_onglet
End Sub
0
Messages postés
1953
Date d'inscription
lundi 3 mai 2010
Statut
Membre
Dernière intervention
12 août 2021
151
Bonjour,

Les "conditions" pour attribuer la couleur sont trop vagues pour qu'on te fournisse un code tout fait, mais tu peux t'inspirer d'autres sujets approchants, p. ex. : https://forums.commentcamarche.net/forum/affich-12887761-excel-couleur-onglet-en-fonction-cellule
0
Messages postés
28
Date d'inscription
lundi 24 juin 2013
Statut
Membre
Dernière intervention
30 août 2013
5
Merci.

En fait, je remplis dans un fichier si l'agent en question à passé la formation.
Une fois Date, type et état rempli, une cellule se met "Valide".
Si une de ces 3 cellules n'est pas remplie, il met 'Non Valide'.
Si rien n'est rempli, il laisse vide.

C'est cette cellule qui change (il y en a donc plusieurs, une pour chaque formation), qui doit etre prise en compte.

Dans la page, une fois toutes les formations "Valide", l'onglet doit devenir vert.
0
Messages postés
12257
Date d'inscription
jeudi 15 mai 2008
Statut
Modérateur
Dernière intervention
3 septembre 2021
2 675
Bonjour,

Je rejoins Zoul67 (que je salue au passage), pour te dire :
Les "conditions" pour attribuer la couleur sont trop vagues pour qu'on te fournisse un code tout fait

Tu dis :
* Si une de ces 3 cellules n'est pas remplie, il met 'Non Valide'
Ceci amène deux questions :
>Qu'elles cellules doivent être complétées?
>Dans qu'elle cellule est affiché 'Non Valide'?

* C'est cette cellule qui change (il y en a donc plusieurs, une pour chaque formation)
Ceci amène deux questions :
> Qu'elle cellule change?
> Il y en a plusieurs : lesquelles?

Il nous faut donc les adresses précises de toutes ces cellules concernées, du genre : A1, B1, C1 etc, et à quoi elles correspondent...

Ou alors, au minimum, de combien de fois 'Valide' doit être écrit dans ta feuille pour que l'onglet cchange de couleur.

Ou encore....

Désolé, nous n'avons pas encode de boule de cristal...
0
Messages postés
28
Date d'inscription
lundi 24 juin 2013
Statut
Membre
Dernière intervention
30 août 2013
5
Au temps pour moi.

Par exemple je remplis : D7(type de formation), D8(date), et D9 (valide, non valide) et ainsi B9 se remplis "Valide" (seulement si les 3 sont remplies).
Si D7 et ou D8 ne sont pas remplies, alors B9 reste vierge.

Si D9 est remplie "Non Valide" alors B9 est également remplie en "Non Valide".
C'est en fonction de B9 que doit être choisie la couleur de l'onglet car il prend en compte toutes les conditions.

Ceci est pour une formation, pareil pour D10, D11et D12 ou B12 se remplie en fonction.

Une fois que B9 et B12 sont "Valide" l'onglet doit être vert. Si l'un des deux est vierge, l'onglet ne change pas. Si l'un des deux ou tous sont "Non Valide" l'onglet doit être rouge.
0
Messages postés
28
Date d'inscription
lundi 24 juin 2013
Statut
Membre
Dernière intervention
30 août 2013
5
J'ai essayé quelque chose comme :

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$D$9" Then
    If Target.Value = VALIDE Then
        ActiveSheet.Tab.ColorIndex = 3
        Else
        ActiveSheet.Tab.ColorIndex = xlNone
    End If
End If
End Sub


Qui ne prend évidemment pas toutes les conditions mais quand je l'execute, EXCEL me demande de créer une macro et ainsi je ne peux pas la faire fonctionner. Je dois mal m'y prendre pour ouvrir ou enregistrer une macro sou worksheet_change
0
Messages postés
28
Date d'inscription
lundi 24 juin 2013
Statut
Membre
Dernière intervention
30 août 2013
5
Au temps pour moi.

Par exemple je remplis : D7(type de formation), D8(date), et D9 (valide, non valide) et ainsi B9 se remplis "Valide" (seulement si les 3 sont remplies).
Si D7 et ou D8 ne sont pas remplies, alors B9 reste vierge.

Si D9 est remplie "Non Valide" alors B9 est également remplie en "Non Valide".
C'est en fonction de B9 que doit être choisie la couleur de l'onglet car il prend en compte toutes les conditions.

Ceci est pour une formation, pareil pour D10, D11et D12 ou B12 se remplie en fonction.

Une fois que B9 et B12 sont "Valide" l'onglet doit être vert. Si l'un des deux est vierge, l'onglet ne change pas. Si l'un des deux ou tous sont "Non Valide" l'onglet doit être rouge.
0
Messages postés
28
Date d'inscription
lundi 24 juin 2013
Statut
Membre
Dernière intervention
30 août 2013
5
Je rajouterais qu'il est important de prendre B9 et B12 en référence car lorsque je rajoute une date de recyclage pour la formation en E7, E8 et E9 par exemple => B9 prend la dernière valeur, soit E9 et plus D9 pour inscrire "Valide" ou "Non Valide"

En lançant la macro, l'onglet doit soit rester au vert ou virer au rouge si l'agent n'est plus valide.
0
Messages postés
12257
Date d'inscription
jeudi 15 mai 2008
Statut
Modérateur
Dernière intervention
3 septembre 2021
2 675
Très bien.
On avance.
Encore une question :
Comment B9 et B12 changent ils?
Par formule? Par saisie? Par macro? Autrement?
0
Messages postés
28
Date d'inscription
lundi 24 juin 2013
Statut
Membre
Dernière intervention
30 août 2013
5
Ils changent avec cela :

=SI(B8="Non évalué";"";RECHERCHE(B8;D8:M8;D9:M9))
0
Messages postés
28
Date d'inscription
lundi 24 juin 2013
Statut
Membre
Dernière intervention
30 août 2013
5
Ca fonctionne plutôt bien merci bcp.

Imaginons qu'au lieu d'avoir juste B9 et B12 j'ai également B15, B18 ... B48

Je ne peux pas tout copier dans une seule ligne, ça bloque à un moment.
Sinon ça fonctionne.
J'avais un peu minimiser le pbm pour que ce soit plus simple à expliquer pour moi ... ;)
0
Messages postés
1953
Date d'inscription
lundi 3 mai 2010
Statut
Membre
Dernière intervention
12 août 2021
151
C'est toujours les multiples de 3 de 9 à 48 ou ça peut aller plus loin ?
0
Messages postés
28
Date d'inscription
lundi 24 juin 2013
Statut
Membre
Dernière intervention
30 août 2013
5
ça ne va pas plus loin que 48.
Ca fonctionne bien pour le moment.

Cependant (petite difficulté supplémentaire...) lorsque pour un agent ça ne va que jusque 32 par exemple. L'onglet reste noir alors que tout est au vert...
Je pense que la formule continue quand même...

Ce n'est problématique que pour les VALIDE car, de toute façon, un seul NON VALIDE rend rouge mais je pense que TOUT doit être VALIDE pour etre vert même ceux n'existant pas sur certaines pages...


Enfin c'est déjà super quand même !!
0
Messages postés
28
Date d'inscription
lundi 24 juin 2013
Statut
Membre
Dernière intervention
30 août 2013
5
Car j'ai copié/collé la formule pour VALIDE et NON VALIDE jusque 48, ça fonctionne mais quand il n'y en a que 32 par exemple, ça ne fonctionne pas si tout est valide quand même...
0