Compter le nombre de cellules colorées (qu'importe la couleur)

[Patrick Bernier]

Bonjour à tous,

Sous Excel (mais ça peut peut être marcher aussi sous Numbers), je cherche à comptabiliser le nombre de cellules colorées, qu'importe la couleur, ça doit être tout simple a priori...
J'ai "réussi" à comptabiliser les cellules "non vides", mais je n'y parviens pas pour les cellules "non blanches"...
Je cherche partout mais je n'y parviens pas,

Si quelqu'un sait,... merci d'avance !

 

[Aliboron]

ça doit être tout simple a priori...

Euh... "Tout simple" pas vraiment, dans la mesure où il n'y a pas de fonction de feuille de calcul qui prenne en compte la couleur de fond. Il faut donc recourir à une macro. Tu peux faire tourner une macro avec le code suivant :

Sub CellulesDeCouleur()
Dim TotalCellules As Integer
Dim Cellule As Range
For Each Cellule In ActiveSheet.Range("A1:F100")
    If Cellule.Interior.ColorIndex <> -4142 Then
        TotalCellules = TotalCellules + 1
    End If
Next Cellule
MsgBox TotalCellules
End Sub

Sachant que la couleur de fond d'une cellule standard a l'indice -4142, tu obtiendras le nombre de cellules dans la plage A1:F100 ayant un fond d'une autre couleur. À adapter selon tes besoins exacts, bien sûr.

Tu peux aussi utiliser cette macro sous forme d'une fonction, avec le résultat directement dans la feuille de calcul :

Function CellulesEnCouleur()
Dim TotalCellules As Integer
Dim Cellule As Range
For Each Cellule In ActiveSheet.Range("A1:F100")
    If Cellule.Interior.ColorIndex <> -4142 Then
        TotalCellules = TotalCellules + 1
    End If
Next Cellule
CellulesEnCouleur = TotalCellules
End Function

Cela te donnera quelque chose comme ça :

​

Si tu as besoin de plus de détails, merci de préciser le contexte (version d'Excel, de Mac OS X, etc.)

 

[Patrick Bernier]

Merci infiniment pour ta réponse Bernard,
Pour répondre à ta question, je suis sous Excel 2011 pour Mac,
J'ai essayé d'ouvrir une macro et d'y coller ta formule, mais j'ai dû mal faire, rien ne se passe,
Idem pour la 2e formule, ... j'ai naïvement copié-collé la formule dans une cellule, mais ça marche po non plus Excel est pour moi du chinois, je l'utilise jusqu'à présent (comme tout le monde je pense...), comme une simple feuille à carreaux
Pourrais-tu me guider un peu (notamment pour ta 2e proposition) ?
Un grand merci

 

[Aliboron]

Il faut coller les deux codes dans un module macro. Pour cela, aller dans le menu "Outils" > "Macro" > "Visual Basic Editor" et faire un clic droit sur le projet correspondant au classeur en cours pour demander l'insertion d'un module :

​

Dans ce module, tu colles les deux macros. Il te faut alors ajuster la plage à tes besoins. En clair, remplacer "A1:F100" par la plage dans laquelle tu veux compter les cellules. Après, tu peux fermer et revenir à ta feuille. Tu pourras soit exécuter la macro à proprement parler (menu "Outils" > "Macro" > "Macros...") soit saisir dans une cellule la fonction =CellulesEnCouleur() comme illustré par ma copie d'écran...

 

[Patrick Bernier]

J'ai copié collé les 2 formules, ainsi :

Code (Text):
Sub CellulesDeCouleur()
Dim TotalCellules As Integer
Dim Cellule As Range
For Each Cellule In ActiveSheet.Range("A1:F100")
If Cellule.Interior.ColorIndex <> -4142 Then
TotalCellules = TotalCellules + 1
End If
Next Cellule
MsgBox TotalCellules

Code (Text):
Function CellulesEnCouleur()
Dim TotalCellules As Integer
Dim Cellule As Range
For Each Cellule In ActiveSheet.Range("A1:F100")
If Cellule.Interior.ColorIndex <> -4142 Then
TotalCellules = TotalCellules + 1
End If
Next Cellule
CellulesEnCouleur = TotalCellules
End Function


J'ai collé ce bloc, dans Outils/Macro/VBE, clic droit, Insérer/Module,

J'ai modifié A1:F100 en : A1:E20

J'ai sauvegardé, (à ce moment, un message : "les macros VB seront supprimées si vous enregistrez le fichier dans ce format. Etes vous sur de vouloir utiliser ce format de fichier ?)

Puis dans la feuille Excel, j'ai collé =CellulesEnCouleur()
dans une cellule, en dehors de A1:E20

Mais ça ne marche pas a priori, j'ai réessayé de différentes manières...
Quand je tente d'exécuter notamment la macro, j'ai un message : "erreur de compilation"
J'ai dû louper un truc...

 

[Aliboron]

Aïe ! Dans la macro, j'ai oublié la dernière ligne (je vais corriger) :
End Sub

Sans ça, ça ne peut pas marcher, effectivement.

Pour l'enregistrement, il faut choisir le format .xlsm pour que les macros soient conservées.

 

[Patrick Bernier]

Merci encore j'ai ajouté en dernière ligne "End Sub"
Enregistré en xlsm,

 

[Patrick Bernier]

Ca marche super
Merci beaucoup

J'ai réussi à activer la macro, et afficher le total directement dans la feuille,...

Comment peut-on utiliser cette formule, pour chaque colonne d'une même feuille Excel ? Je voudrais afficher le total des cellules colorées de la colonne A, le total de la colonne B, etc...
Doit on copier-coller ta 2ème formule, avec en valeur la colonne entière, puis coller la même formule en dessous avec la colonne suivante, etc ?

Merci d'avance

 

[Aliboron]

Ça demande une petite adaptation, pour qu'on puisse transmettre à la fonction la plage concernée pour chaque cas de figure. La fonction devient ceci :

Function CellulesEnCouleur(ByVal MaPlage As Range)
Dim TotalCellules As Integer
Dim Cellule As Range
For Each Cellule In MaPlage
    If Cellule.Interior.ColorIndex <> -4142 Then
        TotalCellules = TotalCellules + 1
    End If
Next Cellule
CellulesEnCouleur = TotalCellules
End Function

Et dans la cellule où on veut voir le nombre de cellules en couleur dans la plage A1:A20, on saisit :
=CellulesEnCouleur(A1:A20)
Et évidemment pour les autres, on adapte...

 

[Patrick Bernier]

Encore merci Bernard,
Ca marche très bien ... mais le résultat (nombre de cellules coloriées) ne s'affiche pas automatiquement, je dois enregistrer, fermer, réouvrir et activer les macros,
Ne peut on pas afficher le résultat aussitôt la cellule coloriée ?
J'ai tenté de copier la ligne "Application.Volatile", mais ça ne marche pas

 

[Aliboron]

Le problème, à la base, c'est que la simple affectation d'une couleur de fond (par le bouton du ruban ou par le menu "Format" > "Cellule..." > "Remplissage") n'est pas considéré comme un changement dans la feuille, ça ne déclenche donc aucun calcul.

En conséquence, on est obligé de forcer le recalcul de la formule d'une façon ou d'une autre. Soit en positionnant le curseur dans la cellule qui affiche le résultat (comme si on voulait en modifier le contenu, éventuellement avec la combinaison Contrôle U) et de valider avec la touche "Entrée". Soit en saisissant quelque chose (n'importe quoi) dans la plage concernée, quitte à l'effacer ensuite.
En ajoutant la ligne "Application.volatile" on élargit un peu les possibilités : en saisissant et validant n'importe quoi n'importe où dans la feuille, on force le recalcul. Bien sûr, on le force aussi avec la combinaison Commande =.

À noter : si on "colorie" la cellule par copier-coller du format d'une autre cellule, cela suffit à déclencher le calcul même sans la ligne "Application.volatile"...

 

[Patrick Bernier]

Ah c'est vraiment dommage !!!! effectivement, j'ai testé, tout fonctionne bien sinon, par copier-coller de cellule colorée ou en écrivant dans la cellule,....
n'y aurait il pas un moyen de ruser ? (automatiser un rafraichissement de la page en permanence ?)
Merci pour ton aide,

 

[Aliboron]

Non, recalculer "en permanence" ne me semble pas une bonne option, ça va couper en permanence de ressources, ralentir, voire bloquer, tout le reste... Commande =, par contre, ce n'est tout de même pas bien contraignant.

 

[Patrick Bernier]

oui, c'est vrai, question d'habitude,

En revanche, ça marche bien sur Mac, mais pas sur Windows, Excel 2012,
J'ai pourtant refait à l'identique,...
J'ai ce message d'erreur, au lancement du fichier xls : "Microsoft Visual Basic - Erreur de compilation : Nom ambigu détecté : CellulesEnCouleur"
Je ne parviens pas à enregistrer en .xlsm, je suis en .xls, ça pourrait venir de là ?
Merci de ton aide

 

[Patrick Bernier]

Est-ce que le retrait pour les lignes :
TotalCellules = TotalCellules + 1
End If
a son importance ?

 

[Aliboron]

J'ai ce message d'erreur, au lancement du fichier xls : "Microsoft Visual Basic - Erreur de compilation : Nom ambigu détecté : CellulesEnCouleur"

Est-ce que tu n'aurais pas copié deux fois la même macro ? Regarde dans le module macro ce qu'il y a.

Je ne parviens pas à enregistrer en .xlsm, je suis en .xls, ça pourrait venir de là ?

Non, pas du tout. .xls est l'ancien format mais il n'y a pas d'incidence sur la possibilité de prise en compte des macros. Par contre, Excel 2012, ça n'existe pas...

Est-ce que le retrait pour les lignes :
TotalCellules = TotalCellules + 1
End If
a son importance ?

Pour la lisibilité, oui. Pour le fonctionnement, aucune importance.

 

[Patrick Bernier]

ok ok ... je vais revérifier ce qu'il y a dans le module macro,
J'ai pourtant copié ta formule, une seule fois, à cet endroit (je vais vérifier ce point, parce qu'au lancement du fichier, Outils/Macro/Exécuter, c'était vide,...), Je n'ai pas Windows sous les yeux, seulement demain matin...
Merci pour tout !

 

[Patrick Bernier]

J'ai copié le fichier de MacOs à Windows.
Sur Mac (Excel 2011), la fonction est parfaitement reconnue.
Sur Windows (Excel 2002), la fonction prend en compte le cumul des cellules coloriées, si et seulement si, on écrit à l'intérieur. Mais pas seulement : je dois colorier puis écrire. Si je fais l'inverse, ça ne marche pas. Si j'enregistre entre deux, ça ne marche pas non plus.
Grand mystère...

 

[Aliboron]

Excel 2002, je ne sais pas (je n'ai plus ça sous la main depuis déjà un bout de temps !) Mais dans Excel 2016 sous Windows, je retrouve exactement le même comportement que dans les versions pour Mac.

 

[Patrick Bernier]

bon, je vais essayer de demander à mon boss une version un peu plus récente alors...
Sinon, j'ai LibreOffice, penses-tu que ça marcherait ? y a t'il quelque chose à adapter selon toi ?