Mise en forme conditinnelle avec VB

**flag007** · 05/09/2015, 17h40

Bonjour,
J'ai un fichier excel et dans 5 colonnes j'ai une mise en forme conditionnelle selon la réponse à une autre colonne.

J'ai une colonne A qui contient des no de contenu.
Colonne B est une caractéristique de mon contenu
Colonnes C à F, je veux mettre une couleur dans le fond de la cellule selon la caractéristique de la colonne B.

Quand la valeur de la colonne B est
I alors couleur de fond de cellule est rouge
P alors couleur de fond de cellule est Bleu
E alors couleur de fond de cellule est Vert
F alors couleur de fond de cellule est Orange

Exemple:

Colonne A	Colonne B	Colonne C	Colonne D	Colonne E	Colonne F
contenu1	I		Hier		Mois		A		JJ
contenu2	P		Demain		Année		B		KK
contenu3	I		Demain		Jour		A		VV
contenu4	E		Hier		Jour		C		FF
Contenu5	F		Demain		Mois		F		TT
Contenu6	P		Demain		Mois		F		BB

Il faut absoluement que ce soit fait en VB, je sais que je peux le faire via les mises en forme conditionnelle.
Je veux que ce soit un bouton macro et en appuyant sur ce dernier la couleur va se mettre dans les cellules.

Merci de votre aide!
Flag

**kiki29** · 06/09/2015, 00h21

Salut, lire, assimiler puis appliquer cette partie de La mise en forme conditionnelle dans Excel

**gFZT82** · 06/09/2015, 08h07

Bonjour,

Pour éviter l'emploi d'un bouton de commande, tu peux utiliser l'évènement Change de la feuille.
Exemple :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Target.Column = 2 Then
        Select Case Target.Value
            Case "I"
                Target.Offset(0, 1).Resize(, 4).Interior.ColorIndex = 3 'Rouge
            Case "P"
                Target.Offset(0, 1).Resize(, 4).Interior.ColorIndex = 33 'Bleu
            Case "E"
                Target.Offset(0, 1).Resize(, 4).Interior.ColorIndex = 43 'Vert
            Case "F"
                Target.Offset(0, 1).Resize(, 4).Interior.ColorIndex = 44 'Orange
            Case Else
                Target.Offset(0, 1).Resize(, 4).Interior.ColorIndex = xlNone
        End Select
    End If
End Sub

Cordialement.

**Theze** · 06/09/2015, 08h25

Bonjour,

Teste ce qui suit :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
 
Sub Formater()
 
    Range("C1:F6").Select
 
    With Selection
 
        .FormatConditions.Delete
 
        .FormatConditions.Add 2, , "=$B1=""I"""
        .FormatConditions(1).Interior.ColorIndex = 3
 
        .FormatConditions.Add 2, , "=$B1=""P"""
        .FormatConditions(2).Interior.ColorIndex = 5
 
        .FormatConditions.Add 2, , "=$B1=""E"""
        .FormatConditions(3).Interior.ColorIndex = 4
 
        .FormatConditions.Add 2, , "=$B1=""F"""
        .FormatConditions(4).Interior.ColorIndex = 46
 
    End With
 
End Sub

Hervé.

**flag007** · 06/09/2015, 17h34

Bonjour,

Wow, merci Hervé, j'y suis presque!

J'ai réussi à appliquer le fond comme souhaité.

J'aimerais ajouter une couleur uniquement si la cellule n'est pas vide.
Par exemple si la cellule C4 ou E5 est vide alors je veux que le fond soit blanc.

Aussi, j'adore l'idée de gFZT82 de ne pas avoir de bouton et que le changement s'appliquer automatiquement, est-ce que je peux appliquer le Private sub proposé?

Finalement, mon nombre de ligne va augmenter avec le temps. Est-ce possible de faire en sorte que ça sélectionne automatiquement la plage?
Il y aura toujours une valeur dans la colonne B. J'ai essayé d'appliquer un Xldown dans la sélection mais sans succès..... je ne suis pas un pro du VB! :-(

Merci grandement de votre aide!

**patricktoulon** · 06/09/2015, 21h38

pour la selection dynamique de la plage
ca n'est pas (xldown) que tu dois utiliser c'est plutot (xlup)
exemple

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

set maplage =range("A1:f"& cells(rows.count,1).end(xlup).row)

il est preferable de partir de tout en bas et remonter a la premiere cellules non vide ,au cas ou tu aurais des cellules vides

si tu par d'en haut avec xldown des la premiere cellules vide ca s'arrete

apres il y a bien d'autre methodes avec usedrange voir aussi specialcells(xltypelastcell) etc.....

**kiki29** · 07/09/2015, 06h20

Salut, toujours d'actualité : Dernière ligne et la dernière colonne renseignées d'une feuille

**flag007** · 07/09/2015, 15h52

Bonjour,
J'ai réussi! Merci à tous!

Toutefois, je constate que les mises en forme s'ajoutent directement dans les conditions de mises en forme du menu Excel.
Je croyais qu'elle s'activerait uniquement en activant la macro manuellement ou via un bouton.

Est-ce qu'il y a un maximum de cellule ou les ressources Excel ne sont pas suffisante pour traiter la mise en forme conditionnelle?
J'ai un I5 avec 4GRam en 32 bits.... est-ce que je peux m'attendre à des problèmes de performance?

Merci pour votre aide!

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
Sub Formater()
 
 
    Range("AT4:BC" & Cells(Rows.Count, 1).End(xlUp).Row).Select
 
 
    With Selection
        .FormatConditions.Delete
 
        .FormatConditions.Add 2, , "=AT4="""""
        .FormatConditions(1).Interior.ColorIndex = 2
 
        .FormatConditions.Add 2, , "=$AK4=""I"""
        .FormatConditions(2).Interior.ColorIndex = 3
 
        .FormatConditions.Add 2, , "=$AK4=""P"""
        .FormatConditions(3).Interior.ColorIndex = 5
 
        .FormatConditions.Add 2, , "=$AK4=""E"""
        .FormatConditions(4).Interior.ColorIndex = 4
 
        .FormatConditions.Add 2, , "=$AK4=""F"""
        .FormatConditions(5).Interior.ColorIndex = 46
 
    End With
 
End Sub

**patricktoulon** · 07/09/2015, 16h37

Bonjour
en effet plus le nombre de lignes sera grand plus le fichier sera long a demarrer puisque plus gros (les mises en forme etant tres gourmande en terme d'octet)
tu a a eu 2 exemple l'un par theze avec la mise en place de la mise en forme par vba

l'autre par gfzt82
changeant la couleur dans l'evenement selection change simplement
afin de minimiser les effets sur la performance je te suggere d'utiliser plutot ce dernier
avec celui ci le code reste inchangée la taille du fichier changera moins exponentiellement qu'avec une MFC
voila

**flag007** · 07/09/2015, 17h07

Bonjour,

En effet, la mise en forme consomme beaucoup.... je constate que la performance est lourde sur mon fichier.

J'ai essayé d'utiliser le code de gfzt82 mais je ne sais pas comment le modifier pour qu'il fasse exactement ce que je veux.
Je n'arrive pas à le faire rouler.

Merci sincèrement de votre aide

**Theze** · 07/09/2015, 17h24

Bonjour,

Le code gfzt82 de légèrement modifié mais qui fonctionne exactement comme à l'origine (à mettre dans le module de la feuille) :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
 
Private Sub Worksheet_Change(ByVal Target As Range)
 
    'si sélection de plus d'une cellule, fin !
    If Target.Count > 1 Then Exit Sub
 
    'si la modif est faite en colonne B...
    If Target.Column = 2 Then
 
        'les quatres colonne de droite (C, D, E et F)
        'vont prendre la couleur qui est fonction de la lettre entrée en cellule B
        With Target.Offset(0, 1).Resize(, 4)
 
            Select Case Target.Value 'UCase(Target.Value)<-- si on veut I et i concidéré comme identique
 
                Case "I": .Interior.ColorIndex = 3 'Rouge
                Case "P": .Interior.ColorIndex = 33 'Bleu
                Case "E": .Interior.ColorIndex = 43 'Vert
                Case "F": .Interior.ColorIndex = 44 'Orange
 
                'ici, tu rajoute de Case autant que tu veux enfin, dans la limite du résonnable !
                'Case "H": .Interior.ColorIndex = 45
                'Case "M": .Interior.ColorIndex = 25
                'etc...
 
                Case Else: .Interior.ColorIndex = xlNone 'rien
 
            End Select
 
        End With
 
    End If
 
End Sub

**flag007** · 07/09/2015, 18h32

OK maintenant j'essais de rouler le code mais ça ne fonctionne pas....
J'ai créé mon bouton macro et affecté le code suivant mais rien ne se passe...

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
 
Sub MFC()
Call Worksheet_Change
End Sub

**gFZT82** · 07/09/2015, 18h45

Bonjour,

L'intérêt d'utiliser l'évènement Worksheet.Change, c'est justement de ne pas avoir à créer un bouton de commande.
La procédure est lancée automatiquement lorsque les cellules de la colonne B sont modifiées par l'utilisateur.

Cordialement.

**flag007** · 07/09/2015, 18h49

OK c'était ma compréhension de la procédure mais ça ne semble pas fonctionner.

Peut-être une contrainte de sécurité... je vais chercher.

**gFZT82** · 07/09/2015, 18h56

Assures-toi :
1) que les cellules de la colonne B sont bien modifiées directement par l'utilisateur.
2) que tu as bien placé la procédure dans le module de la feuille concernée.

Cordialement.

**patricktoulon** · 07/09/2015, 18h57

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
Sub MFC()
Call Worksheet_Change
End Sub

a non c'est pas comme ca qu'il faut voir les choses

ca n'est pas la sub qui appelle l'evenement c'est l'evenement qui appelle la sub

a la limite mettre le code de gfzt82 dans l'evenement change du workbook

en gros
dans le module workbook tu met tu peut meme précisé le nom pour "Sh"

pour l'evenement automatique l'orsque la cellule est modifiée

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
MFC target
End Sub

ou l'evenement du bouton (click)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
Private Sub CommandButton1_Click()
MFC activecell ' a adapter 
End Sub

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
Sub MFC(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Target.Column = 2 Then
        Select Case Target.Value
            Case "I"
                Target.Offset(0, 1).Resize(, 4).Interior.ColorIndex = 3 'Rouge
            Case "P"
                Target.Offset(0, 1).Resize(, 4).Interior.ColorIndex = 33 'Bleu
            Case "E"
                Target.Offset(0, 1).Resize(, 4).Interior.ColorIndex = 43 'Vert
            Case "F"
                Target.Offset(0, 1).Resize(, 4).Interior.ColorIndex = 44 'Orange
            Case Else
                Target.Offset(0, 1).Resize(, 4).Interior.ColorIndex = xlNone
        End Select
    End If
End Sub

c'est pas plus compliqué que cela

on pourrait meme adapter la sub pour injecter en tant qu'argument une plage de cellule ou une seule cellule

**flag007** · 07/09/2015, 19h34

Ok maintenant le code fonctionne. Je le mettais dans module au lieu de la feuille.

Tout ce qu'il me reste c'est de l'adapter pour que la couleur s'applique seulement s'il y a un texte écrit dans la cellule.
s'il n'y a pas de texte, le fond doit rester blanc.

J'ai fait quelques essais sans succès.....
Merci encore!

**Theze** · 08/09/2015, 06h39

Bonjour,

Tout ce qu'il me reste c'est de l'adapter pour que la couleur s'applique seulement s'il y a un texte écrit dans la cellule.
s'il n'y a pas de texte, le fond doit rester blanc.

Mais c'est ce que le code fait avec :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
Target.Offset(0, 1).Resize(, 4).Interior.ColorIndex = xlNone

**gFZT82** · 08/09/2015, 07h48

Bonjour,

Vérifie si ce remplissage n'est pas provoqué par une mise en forme conditionnelle.

Cordialement.

**patricktoulon** · 08/09/2015, 09h34

Re bon jai compris tu veux colorer laligne
Mais si une des celule de c a f est vide la cellule doit rester blanche
C'est bien ca ?

Mise en forme conditinnelle avec VB

Macros et VBA Excel

Discussions similaires

Partager

Partager