Mise en forme conditionnelle VBA

[beann]

Bonjour,

Je voudrais mettre en forme des cellules selon la valeur saisie dans d'autres, malheureusement j'ai épuisé mon quota de mise en forme conditionnelle de 3.


Alors j'ai crée le code VBA suivant:

Private Sub Worksheet_Change(ByVal Target As Range)
' Multiple Conditional Format
Dim rng As Range

If Range("AM8").Value < 6 Then Range("A8:O8").Interior.ColorIndex = 0
If Range("AM8").Value >= 6 Then Range("A8:O8").Interior.ColorIndex = 10

End Sub

Sauf que j'ai 373 cellules concernées et autant de lignes concernées alors je ne souhaite pas réitérer ma formule 373 fois.

Comment je peux l'automatiser?

Et, si jamais et encore mieux, est-ce qu'il existe une solution autre que passer par VBA pour le faire?

Merci.

 

[MJ13]

Re : Mise en forme conditionnelle VBA

Bonjour Beann

Tu peux nommé ta zone ZONE puis faire une boucle de ce type:

for each cell in range("ZONE")
if cell.value>....Code
 
next

 

[JNP]

Re : Mise en forme conditionnelle VBA

Bonjour le fil 🙂,
Quelque chose comme ça

Dim I As Integer
For I = 8 To 313
If Range("AM" & I).Value < 6 Then Range("A" & I & ":O" & I).Interior.ColorIndex = 0 _
    Else Range("A" & I & ":O" & I).Interior.ColorIndex = 10
Next I

à adapter.
Pour se passer de VBA, passer à 2007 ou 2010 😛...
Bonne journée 😎

 

[beann]

Re : Mise en forme conditionnelle VBA

Merci à vous 2, les codes fonctionnent.
Trés bon forum.

 

[beann]

Re : Mise en forme conditionnelle VBA

Euh par contre, j'obtiens bien un code VBA appliqué à des cellules pour en modifier leur couleur selon les valeurs obtenues de telles ou telles autres cellules mais si je rajoute une colonne ou ligne plus tard sur ma feuille, les noms changent et VBA ne suit pas.

Comment faire en sorte que VBA en tienne compte?

 

[MJ13]

Re : Mise en forme conditionnelle VBA

Re Beann,JNP

Vu avec Henry 😉:

Sub Macro1()
'
' Macro1 Macro
'
'
    Range("B6").Select
    Selection.CurrentRegion.Select
    ActiveWorkbook.Names.Add Name:="ZONE", RefersToR1C1:=Selection() '"=Feuil1!R3C2:R17C4"
End Sub

 

[beann]

Re : Mise en forme conditionnelle VBA

Humm, et si on rentre un peu plus dans les détails? Parce que là c'est un peu flou pour moi. Comment j'adapte ce code pour que ca fonctionne?

 

[beann]

Re : Mise en forme conditionnelle VBA

Arf, y'a plus personne?

 

[Efgé]

Re : Mise en forme conditionnelle VBA

Bonjour à tous,
Avec ce que j'ai compris :

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("AM8:AM" & Range("AM" & Application.Rows.Count).End(xlUp).Row)) Is Nothing Then
    Range("A" & Target.Row & ":O" & Target.Row).Interior.ColorIndex = IIf(Range("AM" & Target.Row).Value < 6, 0, 10)
End If
End Sub

A mettre dans le code de la feuille concernée.
Cordialement

 

[beann]

Re : Mise en forme conditionnelle VBA

Ca ne marche pas. ou je n'y arrive pas. aujourd'hui j'ai ce code là:

Private Sub Worksheet_change(ByVal Target As Range)

Dim I As Integer
For I = 8 To 373


If Range("Q" & I).Value = "*" Then Range("Q" & I).Interior.ColorIndex = 15 _
Else: Range("Q" & I).Interior.ColorIndex = 0

If Range("AL" & I).Value >= 6 Then Range("A" & I & ":O" & I).Interior.ColorIndex = 16 _
Else: Range("A" & I & ":O" & I).Interior.ColorIndex = 0

If Range("AL" & I).Value >= 6 Then Range("P" & I).Interior.ColorIndex = 16 _
Else: If Range("Q" & I).Value = "*" Then Range("P" & I).Interior.ColorIndex = 15 _
Else: Range("P" & I).Interior.ColorIndex = 0


Next I


End Sub

Mais si je décide de rajouter des colonnes, le code ne s'adapte pas aux changements de nom de celles-ci. Du coup, "O" devenant "P", "P" devant "Q", etc., je me retrouve avec des couleurs sur des cellules où je n'en veux pas.

 

[Efgé]

Re : Mise en forme conditionnelle VBA

Bonjour à tous,
J'ai peur que sans fichier exemple on ne cherche trèèès longtemps 😉.
Cordialement

 

[Gael]

Re : Mise en forme conditionnelle VBA

Bonsoir à tous,

En nommant les 2 cellules Q8 et AL8, par exemple "Ref1" et "Ref2", on peut essayer le code suivant:

Private Sub Worksheet_change(ByVal Target As Range)
Dim Cell As Range
For Each Cell In Range("Ref1").Resize(373, 1)
If Cell.Value = "*" Then Cell.Interior.ColorIndex = 15 _
Else: Cell.Interior.ColorIndex = 0
Next
For Each Cell In Range("Ref2").Resize(373, 1)
If Cell.Value >= 6 Then
    Range(Cells(Cell.Row, 1), Cells(Cell.Row, 15)).Interior.ColorIndex = 15
    Cells(Cell.Row, 16).Interior.ColorIndex = 16
Else
    Range(Cells(Cell.Row, 1), Cells(Cell.Row, 16)).Interior.ColorIndex = 0
End If
Next

End Sub

Les insertions ou suppressions de colonnes seront répercutées dans les noms Ref1 et Ref2.

@+

Gael

 

[beann]

Re : Mise en forme conditionnelle VBA

Bonsoir à tous,

En nommant les 2 cellules Q8 et AL8, par exemple "Ref1" et "Ref2", on peut essayer le code suivant:

Private Sub Worksheet_change(ByVal Target As Range)
Dim Cell As Range
For Each Cell In Range("Ref1").Resize(373, 1)
If Cell.Value = "*" Then Cell.Interior.ColorIndex = 15 _
Else: Cell.Interior.ColorIndex = 0
Next
For Each Cell In Range("Ref2").Resize(373, 1)
If Cell.Value >= 6 Then
    Range(Cells(Cell.Row, 1), Cells(Cell.Row, 15)).Interior.ColorIndex = 15
    Cells(Cell.Row, 16).Interior.ColorIndex = 16
Else
    Range(Cells(Cell.Row, 1), Cells(Cell.Row, 16)).Interior.ColorIndex = 0
End If
Next

End Sub

Les insertions ou suppressions de colonnes seront répercutées dans les noms Ref1 et Ref2.

@+

Gael

c'est déjà un bon pas en avant mais je n'arrive toujours pas à l'adapter pour que les couleurs s'adaptent automatiquement.
J'ai mis mon précédent fichier ci-joint. J'aimerais que le résultat soit identique à ce qu'il est aujourd'hui mais avec possibilité de rajouter des colonnes sans que mes couleurs ne soient affectées.

 

[Gael]

Re : Mise en forme conditionnelle VBA

Bonsoir Beann, bonsoir à tous,

J'ai repris ton fichier initial et fait les modifs suivantes:

1 - jours fériés
Changement de la formule de calcul du jour de Pâques par une formule plus simple.

La plage de jours fériés a été nommée "Fer"

2 - Suppression de tous les calculs de jours de semaine en colonne AL car le calcul se fera directement dans la macro.

3 - En colonne Q, le caractère "*" se positionne avec la formule suivante:

=SI(ESTERREUR(EQUIV($P8;Fer;0));"";"*")

Une MFC applique la couleur grisée si la cellule contient "*"

4 - La cellule P8 a été nommée "Pivot" puisque c'est la date de base de tous les calculs. Elle se modifie en fonction de l'année choisie en AP8 avec une formule Date:

=DATE(an;1;1)

Une MFC est appliquée sur toute la colonne de dates pour mettre la cellule en grisé si c'est un jour Férié avec la formule:

=NON(ESTERREUR(EQUIV($P8;Fer;0)))

5 - La macro a été modifiée comme suit:

Private Sub Worksheet_change(ByVal Target As Range)
Dim Cell As Range
If Not Application.Intersect(Target, Range("an")) Is Nothing Then
For Each Cell In Range("Pivot").Resize(366, 1)
coul = IIf(Weekday(Cell.Value, vbMonday) > 5, 16, 0)
Range(Cells(Cell.Row, 1), Cells(Cell.Row, Cell.Column)).Interior.ColorIndex = coul
Next
End If
End Sub

Le if intersect sert à ne lancer le calcul que si l'on a modifié l'année.
Coul prend la valeur 16 ou 0 selon qu'il s'agit d'un WE ou un jour de semaine.
Puis on applique la couleur sélectionnée à toutes les cellules depuis la colonne 1 jusqu'à la colonne de la date Pivot.

Si un jour férié tombe un WE il sera en grisé léger comme un autre Jour férié puisque la MFC est prioritaire sur la couleur de fond. Si tu préfères qu'il soit en gris foncé comme un WE, il suffit d'ajouter la condition WE dans la MFC.

Dis-moi si c'est OK.

@+

Gael

 

[beann]

Re : Mise en forme conditionnelle VBA

c'est parfait!!!!!!!! merci beaucoup. me reste plus qu'à essayer de comprendre ton code (pour mon propre apprentissage) et c'est bon. euh par contre, si je peux saouler un tout ptit peu encore, j'aurais préféré mettre le gris foncé comme tu dis mais je comprends pas comment tu appliques un style WE pour la MFC. j'ai office 2003. Enfin merci beaucoup!