Microsoft 365 [VBA] Sous-totaux selon couleurs des cellules au-dessus

[pingouinal]

Bonjour,

Pour un de mes fichiers, j'aurais besoin d'une macro permettant de faire des sous-totaux selon les couleurs des cellules au-dessus.
Dans le fichier joint, j'ai mis un exemple : en colonne A c'est ma base actuelle, en B le résultat que je cherche à atteindre.

Pour résumer :
- La cellule noire tout en bas est le total, elle fait donc la somme des cellules bleues.
- Les cellules bleues font la somme des cellules vertes au-dessus d'elles jusqu'à la prochaine cellule bleue au-dessus. Par exemple la cellule C29 (bleue) fera la somme des cellules vertes C28 et C25 uniquement car C23 est bleue et sera donc un autre sous-total de cellules vertes (C22 et C17).
- Les cellules vertes font la somme des cellules saumon au-dessus d'elles jusqu'à la prochaine cellule verte. Par contre si il n'y a pas de cellule saumon au-dessus la cellule verte doit rester vide (comme dans les cellules C1, C2 et C6).

Malheureusement mes connaissances en VBA sont trop limitées pour atteindre cet objectif. Pensez-vous que c'est toutefois réalisable? Auriez-vous des pistes (voir la macro) pour m'aider?

D'avance merci à tous ceux qui passeront du temps sur mon problème.

 

[Dudu2]

Bonjour,
Voir ce sujet -> https://www.excel-downloads.com/threads/compter-par-couleur-via-une-formule.20059729/post-20450512

 

[JHA]

Bonjour à tous,

Une solution avec des colonnes supplémentaires, si cela peut t'aider.
Le mieux serait de coder ces colonnes supplémentaires mais je ne sais pas le faire.

JHA

 

[pingouinal]

Bonjour à vous deux et merci pour vos propositions.
Malheureusement je ne peux pas ajouter de colonnes.
J'ai également testé la solution proposée par Dudu2 mais sans succès.

Je me rend compte que j'ai peut-être attaqué le problème par un angle trop complexe. En y réfléchissant à nouveau je me dis qu'il y a peut-être une autre piste à suivre. Je reformule donc mon besoin ci-dessous.

Actuellement j'ai une macro qui met la couleur sur chacune de mes lignes (basée sur des comptes comptables). Quand je mets la couleur verte, est-il possible d'enregistrer la cellule en question et quand j'atteins une cellule bleue d'y mettre la formule somme avec la liste des toutes les cellules vertes enregistrées avant?
Je précise que j'ai besoin d'avoir la formule somme écrite dans la cellule bleue et non pas juste le résultat car les cellules saumons (et donc les vertes) seront remplies plus tard.

Dans mon exemple, la macro enregistrerait les cellules A1, A2 et A4 (toutes vertes) et en A5 (bleue) écrirait =somme(A1;A2;A5). Ensuite l'enregistrement des cellules vertes se réinitialiserait pour la suite de la macro.

En parallèle elle enregistrerait A3 pour inscrire =somme(A3) en A4.

Au final il faudrait pouvoir gérer plusieurs enregistrements de cellules distincts : les saumons, les vertes et les bleues.

D'avance merci pour votre aide.

 

[Dudu2]

Je me demande quel énoncé je préfère !
Il y a un problème à la base ! Il n'y a pas d'évènement qui permette de détecter le changement de couleur d'une cellule. Ou alors il faut écrire un truc très spécial à base d'analyse de l'Undo Stack que je ferai dans un 2ème temps.

Dans un premier temps, il faut activer une macro avec un raccourci (genre Ctrl + <lettre>) et demander quelle couleur à placer sur la cellule active parmi une liste de 4 (saumon, vert, bleu, noir).
La macro placerait la couleur et la formule.

Si ça te va, je peux coder.

 

[pingouinal]

Effectivement mes énoncés n'étaient pas très clairs, je m'en excuse. ^^

Pour éviter d'être un assisté complet, j'ai essayé la deuxième méthode de mon côté.
J'ai commencé une macro qui vérifie pour chaque ligne la couleur et si c'est saumon, elle remplit l'adresse de la cellule une variable appelée lvl1 et définie en string.
J'obtiens donc dans mon fichier réel lvl1 = "$K$8;$K$9"
J'ai noté le bout de code suivant : Cells(i,c).value = Application.WorksheetFunction.Sum(Range(lvl1)) en espérant obtenir dans ma cellule verte =somme($K$8;$K$9) mais j'obtiens seulement l'erreur 1004 Methode 'Range' of object '_Global' failed.

Je pense que si j'arrivais à résoudre ce problème pour mettre la formule somme dans mon fichier avec la variable lvl1, je pourrais terminer la macro sans demander l'écriture complète. Et accessoirement je pourrais comprendre ce que j'ai fait de faux.

Voici le bout de code un peu plus complet (comme c'est intégré au reste de la macro, je ne mets bien sûr pas tout) :

Lvl1 = ""
lStart = 6
lEnd = 100

For c = 3 To colEnd

    If Cells(1, c).Interior.ColorIndex <> xlColorIndexNone Then

        For i = lStart To lEnd - 1

            If Cells(i, c).Interior.ColorIndex = Cells(1, c).Interior.ColorIndex Then
                If Lvl1 = "" Then
                    Lvl1 = Cells(i, c).Address
                    Else: Lvl1 = Lvl1 & ";" & Cells(i, c).Address
                End If
            End If

            If Cells(i, c).Interior.ThemeColor = xlThemeColorDark2 Then
                If Lvl1 <> "" Then
                    Cells(i, c).Value = Application.WorksheetFunction.Sum(Range(Lvl1))
                End If
            End If

        Next i

    End If

Next c

 

[Dudu2]

Un essai... Tu tapes Ctrl+w sur la cellule à coloriser.

 

[Dudu2]

Ach ! il faut aussi gérer la couleur de caractères !

 

[pingouinal]

Génial, merci beaucoup.
Je vais pouvoir adapter cela au reste de la macro.
Encore merci!

 

[Dudu2]

Avec un petit UserForm c'est quand même plus ergonomique !

Edit: pour quitter le UserForm sans choisir, il faut utiliser la touche ESCAPE.

 

[Dudu2]

Quelle misère ! Un bug de base sur la 1ère couleur !