XL 2019 Mise en forme conditionnelle sur plusieurs feuilles

[Pierre111]

Bonjour,

J'ai un fichier Excel dans lequel j'ai plusieurs feuilles qui ont la même structure.
Par simplification on va dire que dans chaque feuille du classeur j'ai un nombre en cellule B2 et en cellule C2.
J'ai créé une nouvelle feuille "Paramètre". Dans cette feuille je voudrais rentrer une formule qui va pouvoir être utilisée pour une mise en forme conditionnelle.
Par exemple, si dans ma feuille "Paramètre" je rentre la formule =B2>C2 en cellule A1, je voudrais pouvoir utiliser cette formule de manière indirecte dans mes autres feuilles et que si la formule rentrée dans ma feuille "Paramètre" est vraie pour la feuille en question, la mise en forme conditionnelle est appliquée.

Cela me permettrait d'uniquement modifier la formule rentrée dans la feuille "Paramètre" et que ça s'actualise automatiquement pour mes autres feuilles.

J'espère avoir été assez précis.
Merci par avance pour votre aide !

 

[crocrocro]

Bonjour Pierre,
en pj une proposition, basique.
Le code de la feuille paramètre

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not (Intersect(Target, Range("$A$1")) Is Nothing) Then
        ActualiserFormuleMFC Range("$A$1").Formula
    End If
End Sub

Le code de la macro qui actualise la MFC de toutes les feuilles

Sub ActualiserFormuleMFC(pFormule As String)

Dim WS As Worksheet
Dim MFC_Cible As FormatCondition
'Boucle sur toutes les feuille de calcul du classeur
    For Each WS In ThisWorkbook.Worksheets
        If WS.Name <> "Paramètre" Then
            For Each MFC_Cible In WS.Cells.FormatConditions
                If MFC_Cible.AppliesTo.Address = "$A$1" Then
                     MFC_Cible.Modify Type:=MFC_Cible.Type, Formula1:=pFormule
                End If
            Next MFC_Cible
        End If
    Next WS
   
    Set MFC_Cible = Nothing
    Set WS = Nothing
End Sub

Quelques remarques :
La formule modèle de la feuille Paramètre est en A1
Les MFC des autres feuilles ciblées sont également en A1.
Contraintes (car non contrôlés dans le code VBA) :
- Les MFC en A1 doivent exister préalablement
- être de type Formule
- avoir pour plage de validation $A$1.

A adapter. Si besoin, je peux affiner à condition d'avoir plus de précisons.

 

[crocrocro]

Autre proposition basée sur les hypothèses suivantes :
- La feuille Paramètre est protégée, ne sont accessibles que les cellules modèles
- Les cellules modèles et cibles ont les mêmes adresses.
- Les cellules cibles ont leur MFC préalablement créées (si inexistante ou pas de type formule, elles seront ignorées)
- si plusieurs MFC pour une même cellule cible et si elles sont de type formule, elles seront toutes modifiées
- si la cellule cible a une MFC avec une plage d'application plus grands que la plage cible , la MFC sera dupliquée
Par exemple
Formule en C2 de la feuille Paramètre : =D2>E2
Plage de validité pour C2 dans la feuille Feuill1 : $C1:$C10
La MFC est scindée en deux :
- Plage de validité $C$1;$C$3:$C$10 et Formule = formule avant mise à jour
- Plage de validité $C$2 et Formule = D1>E1 (et non D2>E2)

on peut bloquer avec le code suivant

If MFC_Cible.AppliesTo.Address = pCellule.address then

Le Code

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count = 1 Then
        If Target.HasFormula Then
            ActualiserFormuleMFC2 Target
        End If
    End If
End Sub

Sub ActualiserFormuleMFC2(pCellule As Range)

Dim WS As Worksheet
Dim MFC_Cible As FormatCondition
'Boucle sur toutes les feuille de calcul du classeur
    For Each WS In ThisWorkbook.Worksheets
        If WS.Name <> "Paramètre" Then
            For Each MFC_Cible In WS.Range(pCellule.Address).FormatConditions
                If MFC_Cible.Type = xlExpression Then
                    ' Type formule -> ok
                    MFC_Cible.Modify Type:=MFC_Cible.Type, Formula1:=pCellule.Formula
                End If
            Next MFC_Cible
        End If
    Next WS
    
    Set MFC_Cible = Nothing
    Set WS = Nothing
End Sub

 

[crocrocro]

Nouvelle proposition en pj qui permet de résoudre le problème de plage de la précédente réponse (désolé pour l'accumulation).
Remarques :
- La feuille Paramètre est protégée, ne sont accessibles que les cellules modèles
- les cellules de la feuille Paramètre doivent être en format Texte.
- Les cellules cibles ont leur MFC préalablement créées (si inexistante ou pas de type formule, elles seront ignorées)
- si plusieurs MFC pour une même cellule cible et si elles sont de type formule, elles seront toutes modifiées
- si la cellule cible fait partie d'une MFC avec une plage d'application qui inclut d'autres cellules, la formule sera appliquée à l'ensemble de la plage. Dans ce cas, en fonction de la position relative de la cellule cible dans la plage de validité, les adresses relatives de la formule seront automatiquement adaptées (voir exemple Feuil1).

Le code

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count = 1 Then
        If Target.Value <> "" Then
            ActualiserFormuleMFC Target
        End If
    End If
End Sub

Sub ActualiserFormuleMFC(pCellule As Range)
Dim i As Integer
Dim WS As Worksheet
Dim MFC As FormatConditions
Dim PlageApplication As String

'Boucle sur toutes les feuille de calcul du classeur
    For Each WS In ThisWorkbook.Worksheets
        If WS.Name <> "Paramètre" Then
            ' pour éviter d'avoir une MFC scindée en 2 :
            ' - 1ère boucle sur la plage intersection avec la cellule modèle
            ' - on récupère la plage d'application (contrainte : on considère qu'elle est unique !)
            ' - 2ème boucle avec plage d'application
            Set MFC = WS.Range(pCellule.Address).FormatConditions
            PlageApplication = ""
            ' 1ère boucle sur la plage intersection avec la cellule modèle
            For i = 1 To MFC.Count
                With MFC(i)
                    If (.Type = xlExpression) And _
                        (Not (Intersect(WS.Range(.AppliesTo.Address), WS.Range(pCellule.Address)) Is Nothing)) Then
                        ' on récupère la plage d'application (contrainte : on considère qu'elle est unique !)
                        PlageApplication = .AppliesTo.Address
                        Exit For
                    End If
                End With
            Next i
            If PlageApplication <> "" Then
            ' 2ème boucle avec plage d'application
                Set MFC = WS.Range(PlageApplication).FormatConditions
                For i = 1 To MFC.Count
                    With MFC(i)
                        If (.Type = xlExpression) Then
                            .Modify Type:=.Type, Formula1:=CStr(pCellule.Value)
                        End If
                    End With
                Next i
            End If
        End If
    Next WS
    
    Set MFC = Nothing
    Set WS = Nothing
End Sub

 

[Pierre111]

Bonjour,

Je vous remercie pour vos réponses mais auriez-vous une idée de comment faire pour le faire avec une formule mais sans macro ?

Merci par avance

 

[crocrocro]

Non, désolé

 

[JHA]

Bonjour à tous,

Peut-être

Dans le 1er onglet Feuil1 cellule A1

Créer la/les mise en forme conditionnelle dans cette cellule

Copier cette cellule

Cliquer sur l'onglet Feuil2 (l'onglet Feuil1 ne doit pas être sélectionné) touche Ctrl et sélectionner tous les autres onglets puis touche "entrée"

Clic droit sur la cellule "A1" puis collage special et choisir l'icone mise en forme

La mise en forme est alors copiée dans tous les onglets.

JHA