Microsoft Excel – Farbige Zellen zählen

Heute gibt’s mal einen aktualisierten Artikel, den ich schon mal auf meiner alten Webseite hatte. Weil das Thema grad wieder mal bei mir aktuell war…

Die Anzahl Zellen mit Daten zu zählen ist ja in Excel mit der ANZAHL-Funktion kein Problem, eine Summe über mehrere Zellen zu bilden geht mit der SUMME-Funktion genauso problemlos.

Was aber wenn die Zellen aufgrund ihrer Hintergrundfarbe gezählt werden sollen? Oder es sollen nur die Zellen summiert werden, die eine bestimmte Hintergrundfarbe haben? Excel selbst bietet da keine fertigen Funktionen an. Es geht aber trotzdem, mit ein wenig VBA …

Und wie geht’s?

Fügen Sie die folgende Funktion im VBA-Editor in Ihre Arbeitsmappe ein (oder besser gleich in VBA-Projektmappe, dann stehen die Funktionen immer zur Verfügung). Danach kann die Funktion in Excel genauso genutzt werden wie die SUMME-Funktion. Der Aufruf lautet also „=SummeRot(Zellbereich)“ und es wird die Summe der Werte zurückgegeben, deren Zellenhintergrund rot ist.

Public Function SummeRot(Bereich As Object) As Double

    Dim s As Double
    Dim a As Object

    Application.Volatile

    s = 0

    For Each a In Bereich
        If a.Interior.ColorIndex = 3 Then
            s = s + a.Value
        End If
    Next

    SummeRot = s

End Function

Geht das auch mit Zählen?

Klar. Die folgende Funktion macht das Gleiche wie die SummeRot-Funktion, nur wird hier nicht der Inhalt der Zellen aufsummiert, sondern es werden lediglich die entsprechenden Zellen gezählt. Der Aufruf lautet dementsprechend „=AnzahlBlau(Zellbereich)“.

Public Function AnzahlBlau(Bereich As Object) As Double

    Dim s As Double
    Dim a As Object

    Application.Volatile

    s = 0

    For Each a In Bereich
        If a.Interior.ColorIndex = 5 Then
            s = s + 1
        End If
    Next

    AnzahlBlau = s

End Function

Kann man nur farbige Hintergründe auswerten?

Nein. Genauso lässt sich die Schriftfarbe auswerten. Anstelle des Interior-Objektes (Hintergrund) muss das Font-Objekt (Schrift-Objekt des Zellinhaltes) ausgewertet werden. Die entsprechende Eigenschaft heißt ebenfalls ColorIndex.

Public Function AnzahlSchriftBlau(Bereich As Object) As Double

    Dim s As Double
    Dim a As Object

    Application.Volatile

    s = 0

    For Each a In Bereich
        If a.Font.ColorIndex = 5 Then
            s = s + 1
        End If
    Next

    AnzahlSchriftBlau = s

End Function

Und woher bekomme ich die Farbwerte?

Nun kann man sich für jede beliebige Farbe eine solche Funktion erstellen. Dazu benötigt man aber den jeweiligen Farbindex. Diesen kann man sich aus der Online-Hilfe heraussuchen. Oder aber ganz einfach selbst ermitteln …

Dabei ist zu beachten das verschiedene Office-Versionen abweichende Objektmodell haben und man natürlich das passende Objektmodell nutzen muss

Public Function GetColorIndex(Bereich As Object)

    'Funktion zum ColorIndex ermitteln, nur eine Zelle angeben !!!
    'Die übergebene Zelle muss in der gewünschten Farbe formatiert sein.

    Application.Volatile

    'Ältere Office-Versionen
    GetColorIndex = Bereich.Interior.ColorIndex

    'Neue Office-Versionen ab Office 2007
    GetColorIndex = Application.ThisCell.Interior.ColorIndex

End Function

Da ist doch ein Haken, oder?

Die beschriebenen Funktionen haben allerdings einen kleinen Haken, der nicht verschwiegen werden soll. Excel (hier Version 2000 bis 2010) löst leider keine Neuberechnung der Werte aus, wenn die Hintergrundfarbe einer Zelle oder die Schriftfarbe geändert wird. Wenn also Farbänderungen vorgenommen werden, dann muss danach mit F9 eine Neuberechnung erzwungen werden. Eine andere Lösung habe ich noch nicht gefunden.

Fertige Funktionen

Für Office 2007 und Neuer: Anzahl Zellen im markierten Bereich, die die gleiche Hintergrundfarbe haben wie die Zelle mit der enthaltenen Formel.

Public Function AnzahlBackgroundColor(rngZellen As Object) As Double

    Dim s As Double
    Dim a As Object
    Dim c As Variant

    Application.Volatile
    
    'Farbwert der Eltern-Zelle, wo die Function eingetragen ist, ermitteln
    c = Application.ThisCell.Interior.ColorIndex

    s = 0

    'Range durchlaufen und Zellen zählen
    For Each a In rngZellen
        If a.Interior.ColorIndex = c Then
            s = s + 1
        End If
    Next

    AnzahlBackgroundColor = s

End Function