Registerfarbe aufgrund von Zellwert einfärben (Excel)?
Ich arbeite grade für eine Kollegin an einer Excel-Tabelle (Excel 2010). Sie wünscht sich nun, dass sich unter einer bestimmten Bedingung die Farben der Registerblätter automatisch ändern. Ich habe dafür die gesamte Tabelle schon soweit vorbereitet, dass eine Formel mir bereits die entsprechende Bedingung ausliest und ausgibt.
Ich gehe dabei jetzt mal ins Detail: In allen Tabellenblätter gibt das Feld J19 mir einen Wert, der entweder =0 oder >0 ist. Bei =0 soll sich der Registername rot färben, bei >0 grün.
Natürlich habe ich Google dazu schon befragt, und mir ist mittlerweile klar, dass sich das mithilfe eines Makros machen ließe. Allerdings liegt genau hier das Problem: Ich habe noch nie mit Makros gearbeitet, schon gar nicht welche geschrieben. Ich weiß mittlerweile wie ich auf die Entwicklertools komme, und wie ich das "Makros"-Fenster öffne. Allerdings habe ich auch nach mehreren (Copy&Paste aus dem Netz) Versuchen es nicht geschafft, dass mir in dem entsprechenden Dialog ein Makro zum Ausführen angezeigt wurde.
Ich bräuchte also eine genaue Anleitung, wo ich was eintragen muss. Ich habe die Tabelle extra so gestaltet, dass der Wert mir in jedem Tabellenblatt im selben Feld ausgegeben wird.
Was ich bislang nicht rausgefunden habe: Läuft das Makro immer automatisch? Oder muss man es immer wieder neu aktivieren? Das wäre nämlich kontraproduktiv, denn die Tabelle soll weitergegeben werden und das Einfärben der Register soll eine "Kontrollfunktion für Dummies" darstelen. Wenn es also jedes Mal aktiviert werden muss, wäre es sinnlos.
Ich hoffe sehr, dass mir jemand helfen kann.
Vielen Dank im Voraus!
3 Antworten
Das geht mit einem (allerdings erklärungsbedürftigen) Zweizeiler im Modul jeden Arbeitsblattes:
- Sub Worksheet_activate()
- If ActiveSheet.Range("J19").Value > 0 Then ActiveSheet.Tab.Color =RGB(0, 255, 0) 'Index = 5 ' vbGreen
- If ActiveSheet.Range("J19").Value = 0 Then ActiveSheet.Tab.Color =RGB(255, 0, 0) 'Index = 4 'vbRed
- End Sub 'keine Aktion bei J19<0
Kopiere das hier, öffne den µ-Editor mit Alt+F11: Du bekommst eine Liste aller Blätter angezeigt.
Öffne jedes mit Doppelklick und füge das µ ein. Erstellst du ein neues Arbeitsblatt, muss das auch in dessen Modul reinkopiert werden
Wirkung: Die Färbung erfolgt erst, wenn das Arbeitsblatt verlassen und wieder ausgewählt wird (wählst du es über den Tab aus, passiert das zwar ebenfalls, aber du siehst es nicht, weil der ausgewählte Tab weiß erscheint)
Die RGB-Zahlen kannst Du übrigens bei Hintergrundfarbe einer Zelle als "weitere Farben" wählen und anzeigen lassen. Ich weiß aber nicht, ob xl alle Farbabstufungen akzeptiert oder nur die Grundfarben. RGB(0, 255, 255) =türkis wird akzeptiert, (255, 0, 255) =magenta ebenfalls.
Es gibt auch Alternativen:
- Worksheet_selectionchange()
- Worksheet_change()
- Worksheet_deactivate()
(plus weitere, kriegst du alle im Aufklappmenü im rechten Teil oberhalb angezeigt)
Zum Makro selbst: Normalerweise erfordert if ein Else und ein End if sowie eine neue Zeile nach Then. Da hier aber keine weitere Möglichkeit gefordert wird und nur eine der Bedingungen zutreffen kann, genügt diese einzeilige Variante. (bei J19<0 passiert gar nichts!)
Melde Dich, wenn was nicht klappt oder der Automatismus anders gewünscht ist (es gibt zB auch ein Workbook_open() oder ein Workbook_sheetchange, da bräuchtest du aber eine Schleife, um jedes Blatt zu erfassen, samt Dim einer Variablen)
- kleine Korrektur: colorindex =
... [RGB() als Wert oder r+g*2^8+b*2^16]
- 1 ist schwarz (0, 0, 0)
- 2 ist weiß (255, 255, 255) ... [16777215]
- 3 ist rot (255, 0, 0) ... [255]
- 4 grün (0, 255, 0) ... [65280]
- 5 blau (0, 0, 255) ... [16711680]
- 6 gelb (255, 255, 0)
- 7 magenta (255, 0, 255)
- 8 türkis (0, 255, 255)
- geht bis 56 (stammt noch ais xl5 und wird noch verwendet)
Hallo nochmal, habe eine optimale Lösung für das automatische Ausführen gefunden: Habe den Checkboxen (händisch) über Rechtsklick -> Makro zuweisen einfach das Makro zum Einfärben zugewiesen. Jetzt wird es immer ausgeführt, sobald jemand eine Checkbox anklickt. Funktioniert einwandfrei! :D
Vielen vielen Dank nochmal für alles!
DH! Gute Lösung, wenns funktioniert (muss ich auch immer erst konkret testen)
Stehen in B3, B5, B7, B9 zu ändernde Einträge oder wo stehen die? Wenn Die Einträge dort konstant bleiben, sollte -ganz ohne Checkboxen!
Ansonsten wäre ein
- Worksheet_Change(ByVal Target As Range)
- if Intersect(Target, Range("A2:A10")) is Nothing then exit sub
- ...Fortsetzung wie gehabt...
eine gängige Lösung. Auch nicht allzu kompliziert (aber nicht getestet)(und A dürfte natürlich woanders sein, nämlich am Ort der Einträge)
Für spezifischere Lösungen bräuchte man mehr Infos zu Originaleinträgen, ob sich dazwischen was ändert, unter welchen Bedingungen jeder Eintrag gezählt werden soll usw.
Die Lösung über Makro die Tabellenreiter zu färben ist aus meiner Sicht nicht wirklich ideal. Kommen Alternativen in Frage, bspw. ein Tabellenblatt anzulegen mit einer Übersicht welche Tabellenreiter grün / rot sind? Das bekommt man ggf. mit Formeln hin und muss die Datei nicht mit Makros versehen.
Wenn es denn die Lösung über das Färben sein soll: Durch was soll die Überprüfung ausgelöst werden? Reicht es beim Öffnen der Datei oder müssen sämtliche Änderungen überwacht werden? Das ist etwas umständlicher.
Sub faerben()
For i = 1 To Sheets.Count
If Sheets(i).Range("J19").Value = 0 Then
sheets(i).Tab.Color = vbRed
Else: Sheets(i).Tab.Color = vbgrenn
End If
Next
End Sub
Zu klären ist nun wie gesagt die Art des Aufrufs und was ggf. noch passieren soll, wenn der Wert in J19 kleiner 0 ist (oder ist das ausgeschlossen?)
Hi,
auch dir erstmal vielen Dank für deine Antwort. Tatsächlich ist es (theoretisch) ausgeschlossen, dass der Wert für J19 kleiner ist als 0. Wie ich oben in einem Kommentar schrieb, ist es im Prinzip ein Counter, der angesprochene Felder ausliest und je nach Ergebnis (Wahr/Falsch) 1 bzw. 0 vergibt und entsprechend addiert.
Die beiden für mich sinnvollsten Varianten zum Auslösen des wären das Wechseln des Tabellenblattes oder (optimalste Lösung) wenn der Wert von J19 sich ändert.
Hallo nochmal, habe eine optimale Lösung für das automatische Ausführen gefunden: Habe den Checkboxen (händisch) über Rechtsklick -> Makro zuweisen einfach das Makro zum Einfärben zugewiesen. Jetzt wird es immer ausgeführt, sobald jemand eine Checkbox anklickt. Funktioniert einwandfrei! :D
Endlich... mal eine Frage, die vernünftig formuliert und frei von Rechtschreibfehlern ist. Bravo!
Hätten Professor Iamiam (DH) und Ninombre (DH) nicht schon so schön geantwortet, hätte sogar ich nachgedacht...
Nur nebenbei: bei Verwirklichung der Antworten darauf achten, dass die Datei anschließend als "...xlsm" (mit Makros) zu speichern ist.
Gruß aus Berlin
Danke für den Hinweis mit dem .xlsm - das wäre mir wirklich flöten gegangen, hättest du mich nicht darauf hingewiesen.
Und ja, da ich selbst regelmäßig über den Fragen die hier gestellt werden verzweifle und mich immer beherrschen muss, nicht zum Troll zu werden, versuche ich meine Fragen immer möglichst genau und eindeutig zu stellen :)
Hi,
zunächst mal vielen Dank für deine ausführliche (und sehr verständliche) Antwort. Habe alles so gemacht wie du sagtest und es funktioniert genau so, wie du sagtest. Es färbt sich rot/grün, wenn ich es abwähle und dann wieder anwähle.
Dass bei J19<0 nichts passiert ist super, aber eigentlich ist dieser Fall auch nicht möglich. Ich habe in der Tabelle mit Checkboxen gearbeitet, und J19 liest die über eine einfach wenn-Funktion für jedes Tabellenblatt aus und addiert sie.
[=WENN(B2=WAHR;1;0)+WENN(B4=WAHR;1;0)+WENN(B6=WAHR;1;0)+WENN(B8=WAHR;1;0)+WENN(B10=WAHR;1;0)]
Wenn also alle Checkboxen aktiviert sind, ist das Ergebnis in diesem Falle 5, wenn keine aktiviert ist, ist es natürlich 0.
Das Makro zum Einfärben soll ein Kontrollmechanismus sein, welche Tabellenblätter bereits bearbeitet wurden und welche nicht. (Die Menschen, die damit arbeiten müssen sind nicht die hellsten Kerzen auf dem Kuchen und vergessen gerne die Hälfte)
Das bedeutet allerdings auch, dass man da am Automatismus noch was schrauben müsste. Ich denke, es würde reichen, wenn das Makro immer dann ausgeführt wird, wenn das Blatt gewechselt wird, aber optimal wäre es natürlich, wenn sich das Register färben würde, sobald der Wert in J19 von 0 auf 1 (oder höher) springt.
Hast du da einen entsprechenden Tipp?