Office: WENN mit zellenhintergrundfarbe als bedingung

Schönen Tag

Ich erstelle gerade einen Dienstplan, wo die einzelnen Dienste (Nachtdienst, Hauptdienst...) mit einer bestimmten Zellen-Hintergrundfarbe vermerkt sind.

Nun will ich dass Excel automatisch eine bestimmte Stundenanzahl, abhängig von einer Zellen-hintergrundfarbe, also vom jeweiligen Dienst, hinschreibt.

Also konkret - WENN(bgcolor=rot;6 stunden)

Ist soweit ich mitbekommen hab nur mit einem Modul möglich.
Wäre sehr dankbar wenn mir jemand helfen könnte, hab mit VBA leider noch nichts am Hut.

Peace.

 

Hallo,

im folgenden Beispiel enthält die Zelle F3 eine roten -3 auf gelbem Hintergrund:

	F	G	H	I
3	-3		255	=Rot
4			65535	=Gelb
5			6 Stunden
6			4 Stunden

Zelle	Formel
H3	=Font_Farbe(F3)
H4	=Back_Farbe(F3)
H5	=WENN(Font_Farbe(F3)=RGB_Farbe(255;0;0);"6 Stunden";"10 Stunden")
H6	=WENN(Back_Farbe(F3)=RGB_Farbe(255;255;0);"4 Stunden";"7 Stunden")

Damit das Ganze funktioniert, sind folgende 3 VBA-Funktionen nötig:

Code:

Public Function RGB_Farbe(Rot As Byte, Grün As Byte, Blau As Byte) As Long
  RGB_Farbe = RGB(Rot, Grün, Blau)
End Function
Public Function Font_Farbe(Rg As Range) As Long
  Application.Volatile True
  Font_Farbe = Rg.Font.Color
End Function
Public Function Back_Farbe(Rg As Range) As Long
  Application.Volatile True
  Back_Farbe = Rg.Interior.Color
End Function

Falls Du nicht weißt, wie man diese speichert, hier das Installations-Prozedere:
1) Mit der Maus die 3 Funktionen markieren und die Tastenkombination Strg+C drücken, um sie in die Zwischenablage zu kopieren.
2) Wechsle wieder zurück in Dein Excel-Arbeitsblatt
3) Drücke die Tastenkombination Alt+F11, um den VBA-Editor aufzurufen
4) Im Menü des VBA-Editors wähle "Einfügen > Modul", sodass sich das Fenster für das allgem. Codemodul der Excel-Anwendung öffnet.
5) In diesem Fenster drücke die Tastenkombination Strg+V, um das Makro aus der Zwischenablage im Codemodul-Fenster einzufügen.
6) Wechsle wieder zürck ins Excel-Arbeitsblatt - die Funktionen sind installiert in dieser Excel-Mappe.

7) Du kannst jetzt die 3 Funktionen verwenden, so wie oben als Beispiel beschrieben.

Verwendung der Funktionen:
1) Du musst, damit die 2 Funktionen "Font_Farbe(...)" und "Back_Farbe(..)" die aktuellen Farbwerte aus der betreffenden Zelle zurückliefern, entweder die Taste F9 (=Blatt berechnen) drücken oder sonst wo im Arbeitsblatt etwas eingeben.
2) Funktion RGB_FARBE(Rot;Grün;Blau):
liefert eine Farbkonstante zurück, die sich zusammensetzt aus den 3 Farbkomponenten Rot, Grün, Blau: jede dieser 3 Komponenten kann einen Wert 0-255 annehmen (0=keine Farbe; 255=Volle Farbe).
3) Funktion FONT_FARBE(Zelle):
liefert die Vordergrundfarbe (also die Farbe der Schrift) der betreffenden Zelle zurück.
4) Funktion BACK_FARBE(Zelle):
liefert die Hintergrundfarbe der betreffenden Zelle zurück.

 

Danke

Danke Exl121150 für deine vollkommene Antwort, hab den Code und die Lösung beim ersten Blick nachvollziehen können und ausprobiert.

Funktioniert auch prima, nur hab ich nun festgestellt dass ich bei meiner Fragestellung einen Punkt ausgelassen habe.

Nämlich werden die Dienste mit einer Gültigkeitsliste in jeder Zelle eingetragen, durch bedingte Formatierung ändert sich dann die Hintergrundfarbe, worauf sich automatisch in die Zelle daneben die Stunden eintragen sollen. Theoretisch hast du mir die Lösung angeboten, praktisch lässt sich das auf Gültigkeitslisetn nicht anwenden.

Wäre über weitere Lösungsvorschläge dankbar.

Peace.

--edit: Eins noch, ich suche das Add-Inn womit man die Beschränkung auf 3 Bedingte Formatierungen aufheben kann. Hab nur die .ini-Datei gefunden.

 

Hallo,

wenn es sich um bedingte Formatierung handelt, dann muss es ja für die Zelle, die auf diese Weise farblich beglückt wurde, eine Bedingung geben, die zB. zu ihrer Roteinfärbung führt: und genau diese Bedingung kann man anwenden, um die Stundenanzahlen abhängig von der Farbe (zB. rot) zu erhalten.

Bezüglich Add-In für bedingte Formatierung (Name: CFPlus.xla) sollte folgender Link weiterhelfen (ich habe allerdings die angebotene Lösung nicht überprüft):
http://forum.chip.de/office/bedingte-formatierung-andere-schriftart-948895.html
bzw. die Originalseite:
http://www.xldynamic.com/source/xld.CFPlus.Download.html

 

Add-In

Ich glaube wenn ich das Add-In besitze, sind meine Probleme weitgehend gelöst. Doch leider funktioniert nicht mal der Download-Link auf der Originalseite. Also falls das jemand hat, wäre ich sehr dankbar wenn er/sie es uploaden würde!

@Exl: Danke für die Antwort, aber dein Vorschlag geht mir noch nicht so ganz in den Kopf. Ich habe eine Gültigkeitsliste mit diesen Optionen:

ND (Nachtdienst)
HD (Hauptdienst)
BD (Beidienst)
FD ...
U ...
...
...

In der bedingten formatierung frage ich dann den zelleninhalt ab.
Bei ND -> Roter Hintergrund, bei HD -> Blau usw...

aber wie kann ich zusätzlich mit dieser bedingung noch die stunden erhalten?
bei ND in die danebenstehende Zelle, z.b. 3, bei HD, 6 usw...

aber am wichtigsten wär mir mal das Add-In!

Peace.

 

Hallo,

angenommen Deine Gültigkeits-Combobox befinde sich in Zelle D3, in Zelle E3 willst Du die Stunden (auf Basis der Zelle D3). Dann lautet die Formel in E3:
=WENN(D3="ND";3; WENN(D3="HD";6;4))
was bewirken würde, dass, wenn Du in der Combo "ND" auswählst, in Zelle E3 eine 3 steht, wenn Du "HD" auswählst, in Zelle E3 eine 6 steht, wenn Du etwas anderes auswählst, in Zelle E3 eine 4 steht.

oder aber Du entschließt Dich für die konfortablere (und später auch leichter wartbare) Lösung:

	D	E	F	G	H	I	J	K
1
2						ND	3
3	ND	3				HD	6
4						BD	4
5						FD	4
6						U	4
7

Zelle	Formel
E3	=SVERWEIS(D3;I2:J6;2;FALSCH)

Dabei befinde sich Deine Gültigkeitskombo wieder in Zelle D3. In dieser hast Du unter den Einstellungen in der Combo Zulassen: Liste ausgewählt und in der Quelle als Bezugsformel =$I$2:$I$6 eingestellt.

Im Bereich J2:J6 stellst Du neben Deinen Codes für die Gültigkeitscombo die Stundenliste. Mit der SVERWEIS-Funktion in Zelle E3 greifst Du sowohl auf die Auswahl in der Gültigkeitscombo (D3) als auch auf die Code/Stunden-Tabelle in I2:J6 zu und erhältst so die gewünschten Stunden.

Was das Add-In anbelangt, hatte ich Dir zwar über Google die Download-Page herausgesucht in der Hoffnung, dass sie natürlich noch voll existiert. Da ich aber Excel2007 habe und somit das Add-In nicht brauche, habe ich die Seite nicht getestet. Tut mir leid, dass ich Dir da nicht wirklich weiterhelfen konnte.