Monsieur Excel
Pour tout savoir faire sur Excel !

27 novembre 2016

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 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 !