Mise en forme de cellule selon des conditions

Bonjour,

Je souhaite pouvoir mettre en valeur des cellules selon plusieurs conditions.

J'ai réussi a faire une mise en forme conditionnelle très simple avec les formules d'Excel, mais pour ce que je souhaite faire c'est pas gagné

Si dessous un exemple .

Etat initial

Q1Q2Q3Q4Q5CODE
NAKONAOKNAD1
NANAOKKONAD2
NANANANONOUID3
OKOKOKKOKOD1
NAKOKONANAD2
NANAKOOKNAD1

Résultat souhaité

Q1Q2Q3Q4Q5CODE
NAKONAOKNAD1
NANAOKKONAD2
NANANANONOUID3
OKOKOKKOKOD1
NAKOKONANAD2
NANAKOOKNAD1

Ce que j'aimerais faire c'est :

- a chaque fois que dans la colonne "CODE" il y a une cellule qui contient la valeur "D1",

chercher dans les cellules Q2; Q3; Q4; Q5 de la même ligne que la cellule "D1" si il y a la valeur "NA", si oui, changer la couleur de remplissage des cellules Q2; Q3; Q4; Q5 et D1.

Bonsoir,

une mise en forme sur la cellule en dessous de Q1 en formule avec ceci en admettant que ligne d'entête Q1 etc soit la ligne 1 et la colonne A:

=ET(A2="NA";$F2="D1")
Choisissez votre mise en forme
S'applique à : $A$2:$E$7

Pour la colonne F :

=ET(F2="D1")
la mise en forme
s'applique à : $F$2:$F$7

@ bientôt

LouReeD

Merci LouReeD!

J'ai essayé sur un petit coin du fichier original et ça fonctionne a merveille.

Du coup ça soulève un autre problème, le fichier original que je traite contient 160 colonnes et peu aller au delà de 20000 lignes selon la période de l'année;

Une fois toutes les formules écrites, j'ai bien peur que ça fasse planter Excel et au mieux freezer l'appli le temps du traitement...

je me permet de mettre en PJ un exemple au plus proche de ce que je traite.

Bonsoir,

au vu de votre fichier joint, créer en cellule AF2 cette mise en forme :

=ET(AF2="NA";$EE2="D1")
Choisissez votre mise en forme
S'applique à : $AF$2:$ED$22000

et en EE2 celle-ci :

=ET(EE2="D1")
la mise en forme
s'applique à : $EE$2:$EE$22000

Excel devrait réussir à gérer

@ bientôt

LouReeD

Bonsoir LouReeD,

J'ai essayer de faire le job en VBA, j'ai bricolé un début de code ci dessous, mais je bloque sur la coloration conditionnelle des cellules en lien avec la colonne code...

Public Sub cherchEtColore()
Range("F2", Range("F2").End(xlDown)).Select
Dim celcode As Range
Dim celcolor As Range
Dim celNA As Range
For Each celcode In Selection

If celcode = "D1" Then
 numlign = celcode.Row
 Range("A" & numlign & ":" & "D" & numlign).Select

 For Each celNA In Selection
 If celNA = "NA" Then
 numcell = celNA.Column

 Range("A" & numlign & ":" & "D" & numlign).Interior.Color = RGB(255, 0, 0) '<--ici sa bloque
 'MsgBox ("Numéro de colonne " & numcell & ", " & "numéro de ligne " & numlign) <-- ici j'ai bien l'adresse de chaque cellule trouvé avec "NA" en valeur
 'MsgBox ("trouvé " & numlign)
End If
 Next celNA

End If

Next celcode

MsgBox "Reinitialisation couleur"
Range("A2:F14").Interior.Color = xlNone

End Sub

En toute modestie (vu le nom de la procédure...) :

Sub LouReeD()
    Dim Derligne As Long, I As Long, J As Integer
    Derligne = Range("F2").End(xlDown).Row
    For I = 2 To Derligne
        If Cells(I, 6).Value = "D1" Then
            For J = 1 To 5
                If Cells(I, 6).Offset(, -J).Value = "NA" Then Cells(I, 6).Offset(, -J).Interior.Color = RGB(255, 0, 0): Cells(I, 6).Interior.Color = RGB(255, 255, 0)
            Next J
        End If
    Next I

    MsgBox "OK ? Alors reinitialisation des couleurs"
    Range("A2:F14").Interior.Color = xlNone
End Sub

Mais il y a je pense encore plus court...

@ bientôt

LouReeD

Bonjour LouReeD,

Tu as bien raison de signer la procédure, après tout c'est toi qui la faite , et elle marche parfaitement. Peut tu m'expliquer cette boucle :

'ici on dit que c'est la colonne F est que si la valeur est égale a "D1" alors suite ?
 If Cells(I, 6).Value = "D1" Then For J = 1 To 5 
' ci dessous je comprends pas la fonction Offset, ni le(-J)... 
If Cells(I, 6).Offset(, -J).Value = "NA" Then Cells(I, 6).Offset(, -J).Interior.Color = RGB(255, 0, 0): Cells(I, 6).Interior.Color = RGB(255, 255, 0)

Dans le fichier réel je n'aurais pas forcément que des colonnes contiguës a vérifier, comment je pourrais écrire le code ci dessus pour qu'il parcoure par exemple (A:B, C, E)

Bonjour,

En mettant la liste des numéros de colonne dans un tableau, du coup on boucle sur les index du tableau qui lui nous donne le numéro de colonne : Tablo= Array(2,5,6,7,10,11,20)

For j=0 to Ubound(Tablo)-1

If cells(i,j).value ="NA" then

@ bbientôt

LouReeD

LouReeD,

Merci pour cette suggestion, malheureusement je ne sais pas utiliser ce bout de code..

Bonsoir,

votre fichier avec le nouveau code :

@ bientôt

LouReeD

Merci LouReed,

C'est du sur mesure ! Pouvez vous me confirmer que j'ai bien compris le code ?

Ce que j'en comprends :

Déclaration des variables Dim As ...Affectation du Range jusqu'a dernier ligne a la variable DerLigne,

Sub LouReeD()
    Dim Derligne As Long, I As Long, J As Integer, Tablo
    Derligne = Range("F2").End(xlDown).Row

Puis Déclaration des positions de colonne du tableau en une variable nommé (Tablo), on balaye la feuille a partir de la cellule 2 colonne F jusqu’à la dernière ligne ce qui correspond a la variable (DerLigne) :

Tablo = Array(1, 2, 4, 5) 
    For I = 2 To Derligne

Puis on conditionne les actions a exécuter a chaque tour de boucle, c'est a dire : "Si dans les cellules (position de la variable I, colonne 6) il y a la valeur "D1" Alors :

If Cells(I, 6).Value = "D1" Then

On re-balaye la feuille en démarrant a 0 (variable J) jusqu’à la dernière cellule connu Fonction (Ubound) du tableau en variable (Tablo) et Si la cellule parcourue contient la valeur "NA" Alors on colore son intérieur, puis on reboucle...

If Cells(I, Tablo(J)).Value = "NA" Then Cells(I, Tablo(J)).Interior.Color = RGB(255, 0, 0): Cells(I, 6).Interior.Color = RGB(255, 255, 0)
            Next J
        End If
    Next I

Good ?

@ bientôt

LouReeD

LouReed,

Merci mille fois pour ces échanges et pour le temps consacrés, j'ai mis une capture d'écran (vu de loin...) du résultat obtenu dans le fichier original.

macrook

Il me reste plus qu'a affiner en fonction des multiples codes et des conditions par codes.

Good Job LouReed!

Si vraiment il y a trop de colonne, on peut imaginer une boucle sur les en-têtes : si entetes commence par "Q" alors on traite sinon non... Comme cela plus de tableau "fixe" et ça s'adapte à l'ajout et suppression de colonne.

@ bientôt

LouReeD

Synthèse du problème et la solution grâce a LouReed,

je met ce post en résolu .

Tableau de départ.

Q1Q2Q3 pas prise en compteQ4Q5CODE
NAKONAOKNAD1
NANANAKONAD2
NANAOUINONOUID3
OKOKKOKOKOD1

Résultat souhaité :

Remplir en rouge le fond des cellules, si dans la colonne CODE (dans la logique Excel colonne n°6 ou lettre F) il y a la valeur "D1" est que dans la même ligne aux colonnes Q1,Q2,Q4,Q5 (dans la logique Excel colonne 1,2,4,5 ou A,B,D,E) il y a la valeur "NA"

Q1Q2Q3 pas prise en compteQ4Q5CODE
NAKONAOKNAD1
NANANAKONAD2
NANAOUINONOUID3
OKOKKOKOKOD1

Ci dessous le code de LouReed qui marche parfaitement:

Sub LouReeD()
    Dim Derligne As Long, I As Long, J As Integer, Tablo
    Derligne = Range("F2").End(xlDown).Row
    ' ici le tableau est la liste des numéro de colonne A=1 E=5 etc
    Tablo = Array(1, 2, 4, 5)
    For I = 2 To Derligne
        If Cells(I, 6).Value = "D1" Then
            For J = 0 To UBound(Tablo)
                If Cells(I, Tablo(J)).Value = "NA" Then Cells(I, Tablo(J)).Interior.Color = RGB(255, 0, 0): Cells(I, 6).Interior.Color = RGB(255, 255, 0)
            Next J
        End If
    Next I

    MsgBox "OK ? Alors reinitialisation des couleurs"
    Range("A2:F14").Interior.Color = xlNone
End Sub

Merci !!!

Si vraiment il y a trop de colonne, on peut imaginer une boucle sur les en-têtes : si entetes commence par "Q" alors on traite sinon non... Comme cela plus de tableau "fixe" et ça s'adapte à l'ajout et suppression de colonne.

Malheureusement aucune en-tête est identique

Tout dépend de la variation du tableau mais le caractère rechercher peut être un caractère ajouté aux colonnes concernées :

Un espace de façon à être "invisible" à l'œil, ou bien un underscore par exemple. "Faut juste penser" à l'ajouter lors de l'ajout d'une nouvelle colonne...

@ bientôt

LouReeD

J'oubliais : merci pour vos remerciements !

@ bientôt

LouReeD

Tout dépend de la variation du tableau mais le caractère rechercher peut être un caractère ajouté aux colonnes concernées :

Un espace de façon à être "invisible" à l'œil, ou bien un underscore par exemple. "Faut juste penser" à l'ajouter lors de l'ajout d'une nouvelle colonne...

Pour le coup c'est le DEV qui fourni les fichiers sources qui est en charge de ça...

Bonjour,

Je rebondis sur ce post, car je suis parti du code fourni par LouReed pour un nouveau besoin, sauf que je bloque sur un point.

Le script : il vérifie si les cellules des colonnes pré-renseignées sont vides et lorsque c'est le cas, il les colore et écrit dans une colonne/cellule le mot partiel.

J'aimerais que le script lorsque les conditions ci-dessus ne sont pas réunies mettent les cellules en vert et écris "complet" au lieu de partiel, ci-dessous un extrait :

Sub Verif() '(partiel) sur champs vide
    'Déclaration des types de variables
    Dim Derligne As Long, I As Long, J As Integer, Tablo
    'Création de la variable qui donne la dernière ligne du tableau
    Derligne = Range("D2").End(xlDown).Row

    ' les chiffres dans tablo repésente les numéros de colonnes a controler
    Tablo = Array(13, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 42, 43, 44, 45, 47, 48, 49, 50, 51, 53, 54, 55, 56, 57, 58, 59, 60, 62, 63, 64, 65, 66, 67, 68, 71, 73, 75, 76, 77, 78, 80, 82, 83, 88, 90, 91, 93, 94, 96, 101, 103, 107, 109, 111, 113, 114, 116, 117, 119, 120, 121, 122)
    'I représente la cellule de départ de la boucle jusqu'a la derniere cellule de la derniere ligne du tableau
    For I = 2 To Derligne
        'Verifie Si dans la colonne numéro 8(H) la valeur et "A chaud" si non stop si oui on continue
        If Cells(I, 8).Value = "A chaud" Then
            For J = 0 To UBound(Tablo)
                'Si dans les cellule des colonnes du tablo la valeur est "vide" colore les celulles vide et écrire "partiel" dans les cellules de la colonne complétude
                If Cells(I, Tablo(J)).Value = "" Then
                Cells(I, Tablo(J)).Interior.Color = RGB(255, 198, 140): Cells(I, 1).Interior.Color = RGB(255, 198, 140): Cells(I, 2).Interior.Color = RGB(255, 198, 140): Cells(I, 123).Interior.Color = RGB(255, 198, 140): Cells(I, 123) = "Partiel"
           End If
            'Recommence les actions de la boucle For J
            Next J

        ' Recommence la verification de la valeur A chaud
        End If
    ' Recommence la boucle For I

    Next I
    Verif_incoDeux
End Sub
Sub Verif_IncoDeux()
'Déclaration des types de variables
    Dim Derligne As Long, I As Long, J As Integer, Tablo
    'Création de la variable qui donne la dernière ligne du tableau
    Derligne = Range("D2").End(xlDown).Row

    ' les chiffres dans tablo repésente les numéros de colonnes a controler
    Tablo = Array(13, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 42, 43, 44, 45, 47, 48, 49, 50, 51, 53, 54, 55, 56, 57, 58, 59, 60, 62, 63, 64, 65, 66, 67, 68, 71, 73, 75, 76, 77, 78, 80, 82, 83, 88, 90, 91, 93, 94, 96, 101, 103, 107, 109, 111, 113, 114, 116, 117, 119, 120, 121, 122)
    'I représente la cellule de départ de la boucle jusqu'a la derniere cellule de la derniere ligne du tableau
    For I = 2 To Derligne
        'Verifie Si la valeur dans la colonne numéro 8(H) est "A chaud" alors suivant sinon stop
        If Cells(I, 8).Value = "A chaud" And Cells(I, 123).Value <> "Partiel" Then
        'Pour chaque numéro de colonne de la variable Tablo
            For J = 0 To UBound(Tablo)
                'Si dans les cellule des colonnes du tablo la valeur n'est pas "vide" colore les celulles vide en vert et écrire "complet" dans les cellules de la colonne complétude
                If Not IsEmpty(Cells(I, Tablo(J))) Then
                Cells(I, Tablo(J)).Interior.Color = RGB(0, 255, 128): Cells(I, 1).Interior.Color = RGB(0, 255, 128): Cells(I, 2).Interior.Color = RGB(0, 255, 128): Cells(I, 123) = "Complet": Cells(I, 123).Interior.Color = RGB(0, 255, 128)

                End If
            'Recommence les actions de la boucle For J
            Next J

        ' Recommence la verification de la valeur A chaud
        End If
    ' Recommence la boucle For I

    Next I

End Sub

Pour ce qui est du problème, le script s'exécute bien sans erreurs, les champs partiels sont bien renseignés sauf les dits "complets".

Rechercher des sujets similaires à "mise forme conditions"