Formatierung mit VBA zählen oder (?)

[Timon Galeazzi]

Liebes Forum

Ich bin momentan dabei, eine Ferienliste mür die Mitarbeiter unserer Firma zu erstellen und bein beim Versuch gescheitert, Zellen mit einer bestimmten Formatierung (also Farbfüllung) zu zählen.

In der angehängten Liste könnt ihr sehen, dass in der Zeile 2 jeweis die Mitarbeiter eingetragen werden. Darunter sind die Daten des ganzen Jahren, so die Mitarbeiter mit Farbe ihre Ferientage eintragen könne (oder anderes gemäss legende in Spalten X, Y)

also es sollen bespielsweise alle bewilligten Ferientage für jeden Mitarbeiter einzeln in den Zeile 4 gezählt werden.

(die angehänte Liste hat einige der fehlgeschlagenen Versuche drin. kann auch eine "saubere" Liste anhängen)


ich habe dies mit folgenden varianten versucht:

• Über den Reiter Formen einen Neuen Namen definieren welcher die "Nummer" der Farbe ausgiebt.
  

Dies habe ich mit =ZELLE.ZUORDNEN(63;INDIREKT("ZS(-1)";)) gemacht (aus dem internet) unt das hat auch ganz gut funktioniert.

ABER: wenn ich in der oberen Zelle (Siehe anhang) mit der =Zählenwenn Formel alle zellen welche den Wert für die entsprechende Farbe ausgegeben haben zählen will und unten eine Zelle neu einfärbe aktualisiert es die Zahl nicht.
Ausserdem ist die =Farbe Funktion (welche den Wert der farbe ausgeben soll) nach Neustart des Tabellenbaltts nicht mehr aktiv.

Also würde hier eine automatische Aktualisierung und eine Lösung zum Problem, dass die Formel immer raus ist nach neustart, gefunden werden
Die Aktualisierung habe ich mit +(0*JETZT()) versucht, hat aber nicht geklappt.

• Verschiedene neue Formeln über VBA eingefügt:
  

Code:

Public Function SummeWennFarbe(Bereich As Range, _
                              SuchFarbe As Variant, _
                              Optional Summe_Bereich As Range, _
                              Optional bolFont As Boolean = False) _
      As Variant

'© t.ramel@mvps.org, 30.05.2003
'erweitert 01.07.2004, 31.08.2004, 11.12.2004, 18.04.2005
'Funktion zur Anwendung von SUMMEWENN() mit Hintergrund- oder Schriftfarbe
'als Kriterium
'
'Die Parametereingabe erfolgt in derselben Reihenfolge
'wie in der Funktion SUMMEWENN():
' - Der erste Parameter erwartet den Suchbereich
' - Der zweite Parameter erwartet einen Zellbezug
'   (Hintergrund/Schriftfarbe) oder einen Farbindex (Zahl)
'   Farbindex '0' zählt Zellen ohne Hintergrund/Standard-Schriftfarbe
' - Der dritte Parameter erwartet optional den zu summierenden Bereich
' - Der vierte Parameter erwartet Wahr/Falsch für die Festlegung
'   ob nach Hintergrund- oder Schriftfarbe summiert werden soll

'Zur automatischen Aktualisierung im Tabellenblatt den folgenden Term
'anhängen: +(0*JETZT()) und durch F9 drücken die Funktion aktualisieren
'Also z.B. wie folgt: =SummeWennFarbe(A1:A10;A1)+(0*JETZT())

Dim intColor        As Integer
Dim lngI            As Long
Dim Summe           As Variant

  If Summe_Bereich Is Nothing Then Set Summe_Bereich = Bereich

  If bolFont Then
     If IsObject(SuchFarbe) Then
        intColor = SuchFarbe(1).Font.ColorIndex
     Else
        intColor = SuchFarbe
     End If

     For lngI = 1 To Bereich.Count
        If Bereich(lngI).Font.ColorIndex = intColor Then
           Summe = Summe + CDec(Summe_Bereich(lngI))
        End If
     Next

  Else
     If IsObject(SuchFarbe) Then
        intColor = SuchFarbe(1).Interior.ColorIndex
     Else
        intColor = SuchFarbe
     End If

     For lngI = 1 To Bereich.Count
        If Bereich(lngI).Interior.ColorIndex = intColor Then
           Summe = Summe + CDec(Summe_Bereich(lngI))
        End If
     Next lngI

  End If

  SummeWennFarbe = Summe
End Function

Oder mit:

Code:

Function ZählenWennFarbe(Bereich As Range, _
                        SuchFarbe As Variant, _
                        Optional bolFont As Boolean = False) As Double

'Idee von Melanie Breden, © t.ramel@mvps.org / 13.10.2004 / 11.12.2004
'Funktion zur Anwendung von ZÄHLENWENN mit Hintergrundfarbe
'oder Schriftfarbe als Kriterium
'
'Die Parametereingabe erfolgt in derselben Reihenfolge wie in der Funktion
'ZÄHLENWENN():
' - Der erste Parameter erwartet den Suchbereich
' - Der zweite Parameter erwartet einen Zellbezug
'   (Hintergrund/Schriftfarbe) oder Farbindex (Zahl)
'   Farbindex '0' zählt Zellen ohne farbigen
'   Hintergrund/Standard-Schriftfarbe
' - Der dritte Parameter erwartet Wahr/Falsch für die Festlegung
'   ob nach Hintergrund- oder Schriftfarbe gezählt werden soll
'
' Bsp =ZählenWennFarbe(A1:A10;A1;0)   =ZählenWennFarbe(A1;A1:A10;1)
'     =ZählenWennFarbe(A1:A10;3;0)    =ZählenWennFarbe(3;A1:A10;1)

'Zur automatischen Aktualisierung im Tabellenblatt den folgenden Term
'anhängen: +(0*JETZT()) und F9 drücken
'Also z.B. wie folgt: =ZählenWennFarbe(A1:A10;A1)+(0*JETZT())

Dim intColor        As Integer
Dim rngCell         As Range

  If bolFont Then
     If IsObject(SuchFarbe) Then
        intColor = SuchFarbe(1).Font.ColorIndex
     Else
        intColor = SuchFarbe
     End If

     For Each rngCell In Bereich
        If rngCell.Font.ColorIndex = intColor Then
           ZählenWennFarbe = ZählenWennFarbe + 1
        End If
     Next

  Else
     If IsObject(SuchFarbe) Then
        intColor = SuchFarbe(1).Interior.ColorIndex
     Else
        intColor = SuchFarbe
     End If

     For Each rngCell In Bereich
        If rngCell.Interior.ColorIndex = intColor Then
           ZählenWennFarbe = ZählenWennFarbe + 1
        End If
     Next

  End If
End Function

Meine Programmierkenntnisse sind leider ziemlich begrenzt.
Die Codes sind nicht von mir geschrieben sonder kopiert von: http://www.pctipp.ch/forum/showthread.ph...von-Format

Bei diesen hatte ich das Problem, dass ich die Formel nicht anwenden konnte. Sie wurde vorgeschlagen aber der Rückgabewert war immer nur #NAME?


Zu guter Letzt habe ich es auch noch mit folgendem versucht

Code:

Function CountCcolor(range_data As Range, criteria As Range) As Long
   Dim datax As Range
   Dim xcolor As Long
xcolor = criteria.Interior.ColorIndex
For Each datax In range_data
   If datax.Interior.ColorIndex = xcolor Then
       CountCcolor = CountCcolor + 1
   End If
Next datax
End Function

 
hat bis auf 2 Probleme auch ganz gut funktioniert

- Wurde nicht automatisch aktualisiert bei neufärbung einer Zelle
- War nach Neustart von Excel nicht mehr funktionstüchtig


Vielen Dank für eure Hilfe :)

[LCohen]

Tue Dir einen Gefallen und zähle keine Formate. Denn wenn die Formate nicht mit den Inhalten korrespondieren, zählst Du Murks.

Aus letzterem Grund schuf der Erfinder der Flugzeugrampen-Tür, Bill G., die bedingte Formatierung. Diese orientiert sich an Inhalten. Die man wiederum zählen kann.

Jedoch bitte keine Formate. Egal, ob jemand ein VBA dafür geschrieben hat oder nicht.

Aber, Du wirst es ja doch versuchen ...

[maninweb]

Hallo,

grundsätzlich würde ich LCohen zustimmen und das Zählen von z.B. Farbformatierungen nicht per VBA machen.
Wenn VBA, dann über Kriterien und Bedingungen, die Du auf Grund der Inhalte ermitteln kannst.

Zur Info, in der MS Tech Community gibt's auch einen längeren Beitrag dazu, allerdings alles in Englisch.

Gruß

[Timon Galeazzi]

Tue Dir einen Gefallen und zähle keine Formate. Denn wenn die Formate nicht mit den Inhalten korrespondieren, zählst Du Murks.

Aus letzterem Grund schuf der Erfinder der Flugzeugrampen-Tür, Bill G., die bedingte Formatierung. Diese orientiert sich an Inhalten. Die man wiederum zählen kann.

Jedoch bitte keine Formate. Egal, ob jemand ein VBA dafür geschrieben hat oder nicht.

Aber, Du wirst es ja doch versuchen ...

Gut ich habe es auch ohne VBA versucht (=ZELLE.ZUORDNEN(63;INDIREKT("ZS(-1)";)) <- Semikolon, Klammer nicht Zwinkersmiley

damit konnte ich die Nummer der Farbe ausgeben aber auch nur solange ich das Excel nicht neu gestartet habe danach hat es die rausgehauen.

(die Idee hab ich von https://praxistipps.chip.de/excel-farbig...ehts_31568)

Weisst du vielleicht warum es diese immer wieder löscht und wie ich eine automatische aktuallisierung der =ZÄHLENWENN Formel machen kann?

Vielen Dank!

[schauan]

Hallöchen,

nur solange ich das Excel nicht neu gestartet habe danach hat es die rausgehauen

könnte damit zusammenhängen, dass bei Verwendung von Excel4Macro-Funktionen die Datei als xlsm oder xlsb gespeichert werden muss, sonst sind die weg..

wie ich eine automatische Aktualisierung der =ZÄHLENWENN

Sobald sich an den Bestandteilen von ZÄHLENWENN was ändert, wird das auch entsprechend berechnet. Wenn ein Bestandteil eine Excel4macro-Funktion ist, die sich eventuell nicht automatisch aktualisiert, wird sich auch ZÄHLENWENN nicht so einfach aktualisieren. In manchen Fällen hilft dann eine Multiplikation mit (JETZT() = JETZT()) oder etwas in der Art.

[Timon Galeazzi]

Hallöchen,


könnte damit zusammenhängen, dass bei Verwendung von Excel4Macro-Funktionen die Datei als xlsm oder xlsb gespeichert werden muss, sonst sind die weg..

Sobald sich an den Bestandteilen von ZÄHLENWENN was ändert, wird das auch entsprechend berechnet. Wenn ein Bestandteil eine Excel4macro-Funktion ist, die sich eventuell nicht automatisch aktualisiert, wird sich auch ZÄHLENWENN nicht so einfach aktualisieren. In manchen Fällen hilft dann eine Multiplikation mit (JETZT() = JETZT()) oder etwas in der Art.

Hallo Schauan

Vielen Dank für die Antwort.
Also ich habs mal versucht und die Liste als .xlsm und .xlsb gespeichert, jedoch funktioniert das mit der Farbie immer noch nicht. wenn ich den Namen neu definiere funktioniert es gut doch nach Neustart zeigt es nun immer noch #NAME? an.
könnte dies and der Office Version liegen die ich habe? Verwende Office 2010

[Fennek]

Hallo,

[Lästern on] Warum nutzt Du einen Ansatz aus den frühen 90-ern anstelle von VBA?[Lästern off]

Mit VBA kann man die Farbe gut abfragen und dann auch summieren, ist etwas unbeliebt bei Leuten aus dem Datenbankbereich, funktioniert aber ganz gut.

mfg

[Klaus-Dieter]

Hallo Timon,

wie weiter ober schon geschrieben wurde, sind Auswertungen nach Zellformaten ein ganz schlechter Ansatz. Interessant finde ich die Kombination Mutterschaft / Militär. Was machst du wenn eine schwangere Kollegin beim Militär ist? Doppelt rot?

[Timon Galeazzi]

Hallo,

[Lästern on] Warum nutzt Du einen Ansatz aus den frühen 90-ern anstelle von VBA?[Lästern off]

Mit VBA kann man die Farbe gut abfragen und dann auch summieren, ist etwas unbeliebt bei Leuten aus dem Datenbankbereich, funktioniert aber ganz gut.

mfg

Hallo Fennek

Meine VBA Kenntnisse sind leider sehr beschränkt. Ich habe es aber schon auf verschiedene Arten versucht (Siehe originaler Beitrag ganz oben) davon hat aber nichts funktioniert.
hättest du vielleicht einen Vorschlag wie ich das lösen könnte?

Besten Dank
Timon

[Timon Galeazzi]

Hallo Timon,

wie weiter ober schon geschrieben wurde, sind Auswertungen nach Zellformaten ein ganz schlechter Ansatz. Interessant finde ich die Kombination Mutterschaft / Militär. Was machst du wenn eine schwangere Kollegin beim Militär ist? Doppelt rot?

Die steich ich meist raus kommen selten zurück ;)