Ampel via VBA

Forumthread: Ampel via VBA

Ampel via VBA

02.07.2019 15:21:00

Axel

Hallo zusammen,
ich weiß gar nicht, ob das überhaupt geht, was ich möchte...
Vorab schon mal vielen Dank an alle, die sich dieser Herausforderung annehmen.
In der hochgeladenen Datei
https://www.herber.de/bbs/user/130695.xlsx
sind drei Ampeln manuell eingefügt, die sich möglichst abhängig von je zwei Zellwerten (Zeilen 9 und 11) rot, gelb oder grün einfärben sollen. Die Ampel soll die beiden relevanten Werte optisch überlappen, deshalb ist die Position so gewählt. Ich bin aber offen für alternative Vorschläge. Mit einer bedingten Formatierung bin ich nicht zurecht gekommen, weil diese nicht die gewünschte Position einnimmt.
Ampellogik:
Grüne Ampel: Erreichung Kennzahl in % größer gleich 100% UND Veränderung ggü Vj. Positiv
Gelbe Ampel: entweder Erreichung Kennzahl in % kleiner 100% ODER die Veränderung ggü Vj. negativ
Rote Ampel: Erreichung Kennzahl in % kleiner 100% UND Veränderung ggü Vj. negativ
Im Feld B4 ist eine Gültigkeitsliste hinterlegt. Im Zielbild sollen sich die Werte der Tabelle abhängig von dem Wert in B4 anpassen und damit natürlich auch die jeweilige Ampel. Mit SVERWEIS und SUMMEWENNS bekomme ich das hin, aber leider nicht mit INDEX, VERGLEICH. Die Formel in Zelle D9 müsste doch funktionieren, tut sie aber nicht. Ich bin dankbar für den Stupser in die richtige Richtung bzgl. der Formel.
Ich hoffe, ich habe alle relevanten Informationen gegeben und freue mich auf eure Hinweise.
Nochmals vielen Dank vorab an alle!!!
Beste Grüße
Axel

5
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

AW: Ampel via VBA

02.07.2019 15:58:53

Werni
AW: Ampel via VBA

02.07.2019 16:08:57

Werni
Ampel gelöst, INDEX VERGLEICH Formel noch falsch

03.07.2019 08:49:00

Axel
AW: AMPEL

03.07.2019 09:52:55

Werni
Ampel mit VBA gelöst

03.07.2019 10:40:14

Axel

AW: Ampel via VBA

02.07.2019 15:58:53

Werni

Hallo Axel
Einfach mit Bed. Format
Spaltenbreite 2 Spalte C
Dann schreibst du in Zellen C9:C11 ein kleines n
Die Schriftart für diese Zellen = Webdings, Schriftgrösse=14
Ausrichtung = Horizontal + Vertikal Zentriert

	C
9	n
10	n
11	n

Zelle	bedingte Formatierung...	Format
C9	1: =UND(K9>=1;K11>0)	abc
C10	1: =UND(K91;K11>0)	abc
C11	1: =UND(K91;K110)	abc

Zelle	Schriftart	Inhalt
C9	Webdings	n
C10	Webdings	n
C11	Webdings	n

Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg

Das Rechteck kannst über diese Zellen schieben
Gruss Werni

AW: Ampel via VBA

02.07.2019 16:08:57

Werni

Hallo Axel
Kleiner Fehler. Sorry
BedingtesFormat auf Spalte E nicht K.

=UND(E9>=1;E11>0)

Gruss Werni

Ampel gelöst, INDEX VERGLEICH Formel noch falsch

03.07.2019 08:49:00

Axel

Hi Werni,
vielen Dank, dass Du Dich der Sache angenommen hast.
Das ist zwar nicht, was ich suche, aber es hat mir einen Gedankenanstoß gegeben. Ich habe eine Spalte zwischen C und D eingefügt und Deine Anregung umgesetzt.
Bedingte Formatierungen:
D9: =UND(E9 D10: =UND(E90)=UND(E9>1;E11 D11: =UND(E9>1;E11>0)grün
Das funktioniert einwandfrei. Ich denke, damit gehe ich ins Rennen.
Wenn noch jemand eine Idee hat, warum die INDEX VERGLEICH Formel in E9 nicht funktioniert, bin ich dankbar für Tipps.
Beste Grüße
Axel

AW: AMPEL

03.07.2019 09:52:55

Werni

Hallo Axel
Am Anfang war ich irritiert. Da in Zelle D1 Kennzahl1 stand. Ich hab diese Zelle nicht weiter beachtet und hab mich auf die Spalten Referenz eingestellt.
Da du aber vor Allem eine VBA-Lösung wolltest hab ich mich mal dran gesetzt.
Die Ellipsen hab ich umbenannt und zwar als Rote, Gelbe, Grüne für jede Kennzahl.
Also
Kennzahl1 Rote1, Gelbe1, Grüne1
Kennzahl2 Rote2, Gelbe2, Grüne2 etc.
Jetzt, wo ich auch das DropDown Feld in Zelle B4 beachtet hab und auch die Formel in Zelle D9
hab ich mich darangesetzt
Die Formel in Zelle D9 ist somit eine einfache SUMMEWENN Formel
Und die Änderung des DropDown löst eine Caluation aus.
Code im Modul des Tabellenblattes

Private Sub Worksheet_Calculate()
Ampel
End Sub

Code in Modul1

Sub Ampel()
' 1. Ampel ************************************************************************************* _
Tabelle1.Shapes.Range(Array("Rote1", "Gelbe1", "Grüne1")).Fill.ForeColor.RGB = RGB(192, 192, _
192)
If Range("D9") >= 1 And Range("D11") > 0 Then
Tabelle1.Shapes.Range(Array("Grüne1")).Fill.ForeColor.RGB = RGB(146, 208, 80)
End If
If (Tabelle1.Range("D9")  0) Or (Tabelle1.Range("D9") _
>= 1 And Tabelle1.Range("D11") = 1 And Tabelle1.Range("G11") > 0 Then
Tabelle1.Shapes.Range(Array("Grüne2")).Fill.ForeColor.RGB = RGB(146, 208, 80)
End If
If (Tabelle1.Range("G9")  0) Or (Tabelle1.Range("G9") _
>= 1 And Tabelle1.Range("G11") = 1 And Tabelle1.Range("J11") > 0 Then
Tabelle1.Shapes.Range(Array("Grüne3")).Fill.ForeColor.RGB = RGB(146, 208, 80)
End If
If (Tabelle1.Range("J9")  0) Or (Tabelle1.Range("J9") _
>= 1 And Tabelle1.Range("J11")

Gruss Werni
https://www.herber.de/bbs/user/130705.xlsm

Ampel mit VBA gelöst

03.07.2019 10:40:14

Axel

Hi Werni,
das sieht mal richtig genial aus und funktioniert einwandfrei.
Ja ich hab's aktuell auch über eine SUMMEWENN-Formel gelöst. Ich wollte mal was mit INDEX VERGLEICH probieren. Macht aber nichts, es funzt ja.
Ich bau das noch in die Originaldatei ein. Du hast mir sehr geholfen.
Tausend Dank für Deine viele Mühe und Zeit.
Beste Grüße
Axel

Infobox / Tutorial

Schritt-für-Schritt-Anleitung

Vorbereitung der Excel-Datei: Lade deine Excel-Datei hoch und öffne sie. Stelle sicher, dass die Zellen, die du für die Ampelfunktion verwenden möchtest, korrekt formatiert sind.
Bedingte Formatierung einrichten:
- Wähle die Zellen C9:C11 aus.
- Gehe zu Start > Bedingte Formatierung > Neue Regel.
- Wähle "Formel zur Ermittlung der zu formatierenden Zellen verwenden" aus.
- Gib folgende Formeln ein:
  - Für grün: =UND(E9>=1;E11>0)
  - Für gelb: =UND(E9<1;E11>0)
  - Für rot: =UND(E9<1;E11<0)

VBA-Code hinzufügen:

Drücke ALT + F11, um den VBA-Editor zu öffnen.
Füge den folgenden Code in das Modul des Tabellenblattes ein:
```
Private Sub Worksheet_Calculate()
Call Ampel
End Sub
```

Füge den Ampel-Code in ein neues Modul ein:

Sub Ampel()
' Initialisiere Ampeln
Tabelle1.Shapes.Range(Array("Rote1", "Gelbe1", "Grüne1")).Fill.ForeColor.RGB = RGB(192, 192, 192)
If Range("D9") >= 1 And Range("D11") > 0 Then
   Tabelle1.Shapes.Range(Array("Grüne1")).Fill.ForeColor.RGB = RGB(146, 208, 80)
End If
' Weitere Bedingungen hier hinzufügen
End Sub

Formeln für die Zellen anpassen: Achte darauf, dass die Formeln in den Zellen D9:D11 korrekt sind und die gewünschten Werte zurückgeben.

Häufige Fehler und Lösungen

Fehler bei der bedingten Formatierung: Stelle sicher, dass die richtigen Zellen und Formeln verwendet werden. Überprüfe die Zellreferenzen, um sicherzustellen, dass sie konsistent sind.
VBA-Code funktioniert nicht: Überprüfe, ob der Code im richtigen Modul eingefügt wurde und ob die Namen der Shapes korrekt sind.
Ampeln zeigen falsche Farben an: Achte darauf, dass die Berechnungsoptionen auf "Automatisch" eingestellt sind (Formeln > Berechnung > Automatisch).

Alternative Methoden

Wenn du nicht mit VBA arbeiten möchtest, kannst du die Ampelfunktion auch über bedingte Formatierungen und Symbole umsetzen:

Symbole verwenden: Gehe zu Einfügen > Symbole und füge die entsprechenden Ampelsymbole manuell ein.
Datenbalken: Du kannst auch die Funktion der Datenbalken nutzen, um visuelle Hinweise in den Zellen anzuzeigen.

Praktische Beispiele

Beispiel 1: Du hast eine Maschinendatenerfassung in Excel. Verwende die Ampelfunktion, um den Status deiner Maschinen zu überwachen. Zum Beispiel, wenn die Effizienz unter 80% fällt, wird die Ampel rot.

Beispiel 2: In einem Verkaufsbericht kannst du die Ampel nutzen, um die Verkaufsziele visuell darzustellen. Eine grüne Ampel zeigt, dass das Ziel erreicht wurde, während eine rote Ampel darauf hinweist, dass das Ziel nicht erreicht wurde.

Tipps für Profis

Benutzerdefinierte Farben: Du kannst die RGB-Werte der Ampeln anpassen, um sie besser an dein Farbschema anzupassen.
Verwendung in Berichten: Die Ampelfunktion kann auch in Berichten nützlich sein, um schnell wichtige Informationen hervorzuheben.
Word Ampel einfügen: Wenn du die Ampelfunktion in Word benötigst, kannst du ähnliche VBA-Techniken verwenden, um visuelle Elemente zu erstellen.

FAQ: Häufige Fragen

1. Wie kann ich die Ampel-Funktion in Excel ohne VBA nutzen? Du kannst die Ampel-Funktion allein mit bedingten Formatierungen umsetzen, indem du die entsprechenden Farben anpassst.

2. Funktioniert die Ampelfunktion in allen Excel-Versionen? Ja, die beschriebenen Methoden sollten in den meisten modernen Excel-Versionen (2013 und höher) funktionieren.

3. Kann ich die Ampelfunktion auch in anderen Anwendungen wie Word verwenden? Ja, du kannst ähnliche visuelle Elemente in Word erstellen, indem du Formen und bedingte Formatierungen verwendest.

Beliebteste Forumthreads (12 Monate)

Forumthread: Ampel via VBA

5 Beiträge zum Forumthread Beiträge zu diesem Forumthread

Infobox / Tutorial

Ampelfunktion in Excel via VBA

Schritt-für-Schritt-Anleitung

Häufige Fehler und Lösungen

Alternative Methoden

Praktische Beispiele

Tipps für Profis

FAQ: Häufige Fragen

Beliebteste Forumthreads (12 Monate)

5
Beiträge zum Forumthread

Beiträge zu diesem Forumthread