Zar a besoin d’appliquer un format conditionnel aux valeurs de la colonne A d’une feuille de calcul, mais il ne peut pas comprendre quelles pourraient être les règles. Outre la colonne A, il a également des données commençant dans la colonne B et il ajoute périodiquement de nouvelles colonnes de données. S’il y a des données dans toutes les colonnes impaires commençant par B, Zar veut qu’un format soit appliqué dans la colonne A. (B est la colonne de données 1 pour sa feuille de calcul, donc il la considère comme étrange.) S’il y a des données dans toutes les colonnes paires commençant par C, Zar souhaite qu’un format différent soit appliqué dans la colonne A. S’il y a des données dans toutes les colonnes de données commençant par B – quel qu’en soit le nombre – alors il souhaite appliquer un troisième format.

Comme Zar l’a sans aucun doute compris, vous pouvez facilement créer une formule pour déterminer s’il y a des informations dans les colonnes B et C et appliquer la mise en forme en conséquence. En fait, une formule simple comme celle-ci fera l’affaire:

=COUNTA(B1:C1)=2 =COUNTA(B1)=1 =COUNTA(C1)=1

La première formule renvoie True s’il y a des informations à la fois dans B et C, la seconde s’il y a des informations dans B et la troisième s’il y a des informations dans C. Tant que vous sélectionnez « Stop If True » pour chaque règle / formule, alors votre formatage fonctionnera correctement.

La création d’une formule pour plusieurs colonnes au-delà de B et C n’est que légèrement plus difficile. Les trois mêmes types de formules, dans l’ordre, seraient les suivants:

=COUNTA(B1:G1)=6)

=COUNTA(B1,D1,F1)=3 =COUNTA(C1,E1,G1)=3

Vous pouvez facilement ajouter des références de cellule supplémentaires aux formules, si nécessaire. Une telle approche renvoie True dans seulement trois conditions: si TOUTES les cellules de la plage B1: G1 contiennent quelque chose, si TOUTES les cellules impaires (B1, D1, F1) contiennent quelque chose, et si TOUTES les cellules paires (C1, E1, G1) ont quelque chose en eux. Il ne retournera pas True si seules certaines des cellules de la plage contiennent des valeurs. Par exemple, il y a des valeurs dans les cellules B1, C1 et E1, alors il ne retournera pas True et aucun des critères de mise en forme ne sera satisfait.

Bien que tous fonctionnent correctement avec la limitation indiquée, ils ne sont pas exactement ce que Zar recherche: il veut une formule qui détecte le nombre de colonnes utilisées semaine après semaine, alors qu’il continue d’ajouter des données aux colonnes et d’ajuster le formule en conséquence sans qu’il soit nécessaire de modifier manuellement la formule pour prendre en compte les données ajoutées. En d’autres termes, s’il ajoute des données à la colonne H, il souhaiterait que les formules soient automatiquement ajustées pour tenir compte de la colonne ajoutée:

=COUNTA(B1:H1)=7)

=COUNTA(B1,D1,F1,H1)=4 =COUNTA(C1,E1,G1)=3

C’est évidemment un besoin plus complexe. La meilleure façon d’aborder le problème est peut-être de créer une fonction définie par l’utilisateur (une macro) qui peut examiner une plage de cellules et déterminer si l’un des trois critères est satisfait.

Considérez la macro suivante:

Function CellChk(crng As Range) As String     Dim iNumOdds As Integer     Dim iNumEvens As Integer     Dim iOdds As Integer     Dim iEvens As Integer     Dim iTots As Integer     Dim iTotCells As Integer     Dim rWork As Range     Dim rCell As Range     Dim iLastCol As Integer     Dim sTemp As String

iOdds = 0     iEvens = 0     iTots = 0

' Figure out the real last column in the worksheet and set range     iLastCol = ActiveSheet.Cells.Find(What:="*", _         SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, _         LookIn:=xlFormulas).Column     Set rWork = Range(Cells(crng.Row, 2), Cells(crng.Row, iLastCol))



iTotCells = rWork.Count     iNumOdds = (iTotCells + 1) \ 2    ' Number of odd columns     iNumEvens = iTotCells - iNumOdds  ' Number of even columns

For Each rCell In rWork         If rCell <> "" Then             If ((rCell.Column - 1) Mod 2) = 1 Then                 iOdds = iOdds + 1             Else                 iEvens = iEvens + 1             End If             iTots = iTots + 1         End If     Next rCell

sTemp = ""

If iTots = iTotCells Then         sTemp = "t"

ElseIf iOdds = iNumOdds Then         sTemp = "o"

ElseIf iEvens = iNumEvens Then         sTemp = "e"

End If     CellChk = sTemp End Function

Vous utilisez la macro en lui passant une adresse dans la ligne que vous souhaitez vérifier.

Ainsi, par exemple, si vous appliquiez la règle de mise en forme conditionnelle à la cellule A3, vous transmettriez à la macro une adresse de B3 ou C3 – tout sauf A3, car cela entraînerait une référence circulaire. La macro recherche la dernière cellule utilisée dans cette ligne, puis détermine combien de cellules paires et impaires contiennent quelque chose. La macro renvoie l’une des quatre valeurs; si le premier critère est satisfait (toutes les cellules de la ligne commençant par la colonne B contiennent quelque chose) alors un « t » est renvoyé. Si toutes les colonnes impaires (avec B étant la première colonne impaire) contiennent quelque chose, alors « o » est renvoyé. Si toutes les colonnes paires (avec C étant la première colonne paire)

avoir quelque chose en eux, alors « e » est retourné. Si aucun des trois critères n’est satisfait, la fonction ne renvoie rien.

Vous devrez toujours configurer trois règles de mise en forme conditionnelle qui reposent sur l’évaluation d’une formule. En voici trois que vous pouvez utiliser avec cette macro:

=CellChk(B1)="t")

=CellChk(B1)="o")

=CellChk(B1)="e")

Ces exemples concernent l’application d’un format conditionnel à la cellule A1; ajustez les références de cellule à la ligne correcte que vous souhaitez que la macro analyse. N’oubliez pas que même si vous spécifiez une seule cellule (B1 dans ces exemples), la macro calcule le nombre de cellules de la ligne à examiner.

_Note: _

Si vous souhaitez savoir comment utiliser les macros décrites sur cette page (ou sur toute autre page des sites ExcelTips), j’ai préparé une page spéciale qui comprend des informations utiles.

lien: / excelribbon-ExcelTipsMacros [Cliquez ici pour ouvrir cette page spéciale dans un nouvel onglet de navigateur].

ExcelTips est votre source pour une formation Microsoft Excel rentable.

Cette astuce (5945) s’applique à Microsoft Excel 2007, 2010, 2013, 2016, 2019 et Excel dans Office 365.