Opérations sur cellules colorées

On regrette parfois dans Excel de ne pas disposer de formules réalisant des opérations en fonction de la couleur de fond de la cellule, ou de la couleur de la police.

Ce problème est résolu aujourd’hui grâce à une fonction personnalisée (UDF en anglais pour User Defined Function) créée par Philip Treacy, le mari de Mynda, la MVP australienne qui tient avec lui l’excellent blog MyOnlineTrainingHub.

Nous voyons dans le tableau ci-dessous les résultats obtenus avec cette fonction, dont nous avons illustré la formule de chaque colonne via trois commentaires.

Remarque 1 – Je n’ai francisé ni le nom de la fonction ni celui de ses arguments afin de rester compatible avec des modèles provenant d’autres personnes utilisant cette fonction personnalisée.

Voici le code de la fonction. Bien entendu, si vous voulez la rendre accessible à tous vos modèles Excel, je vous conseille de la loger dans votre classeur de macros personnelles.

' Auteur : Philip Treacy

' http://www.myonlinetraininghub.com/count-sum-and-average-colored-cells

' Fonction personnalisée traduite et simplifiée par Hervé Thiriez

'

Function ColorMath(InputRange As Range, ReferenceCell As Range, Optional Action As String = "S")

   

    Application.Volatile

   

    ' Action : S pour somme, A (Avg) pour moyenne, C pour compter

    ' Sans le troisième argument, c'est la somme qui est effectuée

   

    Dim ReferenceColor As Long

    Dim CellCount As Long

    Dim Result As Variant

    Dim Cell As Range

    

    Action = UCase(Action)

    ReferenceColor = ReferenceCell.Interior.Color

       

    If Action = "S" Or Action = "A" Then

        For Each Cell In InputRange

            If Cell.Interior.Color = ReferenceColor Then

                Result = Result + Cell.Value

                CellCount = CellCount + 1

            End If

        Next Cell

    End If

           

    If Action = "C" Then

        For Each Cell In InputRange

            If Cell.Interior.Color = ReferenceColor Then Result = Result + 1

        Next Cell

    End If

           

    If Action = "A" Then Result = Result / CellCount

    ColorMath = Result

End Function

Remarque 2 – Cette fonction personnalisée fonctionne quand les couleurs de fond sont « naturelles », mais pas quand elles sont obtenues par des formats personnalisés !

En effet, selon le VBA, la commande suivante permet d’obtenir la couleur de fond d’une cellule :

CellColor = ActiveCell.DisplayFormat.Interior.Color

Sauf que, dans une fonction personnalisée, cette commande renvoie l’erreur  #VALUE !

Encore un mystère de Microsoft !