Farbige Skala in VBA

[HansMartin888]

Hallo liebe Forengemeinde,

gibt es mit VBA die Möglichkeit, eine farbige Skala mit einem kleinen "Anzeiger" zu machen, basierend auf einer Wertetabelle (siehe Bild)?

Der Code sollte in etwa folgende Schritte machen:
1) Übertrage die Farbskala (5 Farben) jedes Wertes von der "Legende" zur "Auswertung" rüber
2) Wenn im roten Bereich dann kann der Anzeiger in der Mitte platziert werden
3) Wenn im gelb-grünen Bereich dann soll der Anzeiger den "beobachteten Wert" in der Auswertung genau nach der Range aus der Legende justieren

Zusatzbedingung:
Während die Legende immer an Ihrem Platz bleibt, muss ich die "Auswertung" wenn neue Werte anstehen immer wieder mal im Tabellenblatt vervielfältigen können (während die alte Auswertung auch bestehen bleibt). Alle Auswertungen sollen aber immer von der Legende die Basiswerte für die Skala bekommen.


Könnte mir da jemand bei diesem Code helfen?

Liebe Grüße

[d'r Bastler]

Moin HansMartin,

das kann zu einer ziemlich aufwändigen Bastelei werden, wenn das mit VBA realisiert werden soll.

Der fixe Bezug auf die Legende lässt sich in den Auswertungszellen durch das Prefix $ lösen. Die zwei Darstellungsversionen mit Select Case oder if...then. Für die Wert-genaue Position der Markierung geht über Len/Left/Mid/Right. Dafür schreibe ich Dir heute Abend mal ein Beispiel, das Du dann für Dich anpassen kannst. Im Moment geht mein frischer Erdbeerkuchen vor

Grüße

[d'r Bastler]

'n Abend dann auch, HansMartin!

Habe da mal was gebastelt und würde mich über eine Rückmeldung freuen. Es sind noch nicht alle Punkte Deiner Anfrage erledigt, aber der komplizierte Teil funktioniert.

@die helfenden Kollegen: Ich habe auf die Schnelle keine Möglickeit gefunden, in einem String ein Zeichen an einer bestimmten Position zu ersetzen und deshalb einen Zu-Fuß-Workaround mit Left&Right/Pos gebastelt. Vielleicht hat eine(r) die Syntax für Replace oder Change parat und kann hier helfen?

Im Anhang sind die jeweligen Grenzwerte variabel. Also einfach ausprobieren.

Grüße

Nachtrag: Mein Left&Right/Pos-Konstrukt kann man mit

Code:

Cells(x, x) = Left(Cells(x, x), Len(Cells(x, x)) - p) & Replace(Right(Cells(x, x), p), "-", "|")

vereinfachen.

[Elex]

Hallo

Wer Excel als Spielwiese nutzen möchte braucht Kreativität. Hier mal mein Ansatz.
Die Formel in K2 kann auch so aussehen.
=WIEDERHOLEN(" ";WAHL(2+((J2F2);11;T2;119))&"|"

Gruß Elex

[HansMartin888]

Hallo und danke für Eure Antworten und vor allem die Mühe! Finde beide Lösungen echt interessant und kreativ, viele Wege führen nach Rom ;) 

- Es wird öfters vorkommen, dass ich in der "Legende" die Farben des gelb-grünen Bereichs ändern muss. Dann soll in allen "Auswertungen" die Farbe automatisch mit verändert werden. Denn wenn ich mal mehrere Auswertungen auf dem Tabellenblatt habe, müsste ich das jedesmal manuell bei allen ändern, was ein großer Aufwand ist und auch die Fehleranfälligkeit steigt. Daher wäre es klasse wenn der Code die Farbe aus der Legende im ersten Schritt übernimmt.

Bei der ersten Variante finde ich nicht ganz ideal dass die Zeiger in der Zelle nicht von ganz links nach ganz rechts gehen, denn das erschwert die Einschätzung der Werte auf den ersten Blick.

Könnt ihr grob abschätzen, wie hoch der Aufwand ist, das gewünschte so umzusetzen, dass der Zeiger wenigstens in den 3 Zellen die den gelb-grünen Bereich darstellen exakt nach der Legende positioniert wird, unabhängig davon ob man nachträglich die Breite einer Spalte der Auswertung ändert?

Viele Grüße

[d'r Bastler]

Moin HansMartin,

Danke für die Rückmeldung!
Die Spaltenbreite der Farbskalen wird aus den Längen der Werte berechnet. Und nachdem numerische Werte nicht alle so schlank sind wie - oder |  wird natürlich Platz verschwendet. Daher die nicht-lineare Darstellung.
Schnelle Workarounds:

• Ersetze im VBA Code die beiden Zeichen durch z.B. mehrfache Platzhalter oder durch Symbole, die die Breite von Groß-M oder -W haben
  
• Berechne die Spaltenbreite mit Länge * 0.5 (bzw. einem passenden Wert)
  

Ansonsten fällt mir nur ein, einen Fortschrittsbalken zu simulieren. Das würde aber bedeuten, dass Du für jeden Wert eine schmale Zelle anlegen musst, die dann eingefärbt wird. Und dann wird's aufwändig, gerade wenn Du mehrere Auswertungen auf einer Seite haben willst, möglicherweise mit unterschiedlichen Wertlängen. Dann müsstest Du u.U. für die Seitenformatierung auf verbunden Zellen ausweichen, auf die man aber tunlichst verzichtet.

Schöne Grüße

[d'r Bastler]

Schau Dir mal Version2 an ...

  Farbskala_2.xlsb (Größe: 20,22 KB / Downloads: 1)

Grüße

[schauan]

Hallöchen,

Es wird öfters vorkommen, dass ich in der "Legende" die Farben des gelb-grünen Bereichs ändern muss. Dann soll in allen "Auswertungen" die Farbe automatisch mit verändert werden.

Meinst Du wirklich, dass Du die Farbe ändern willst? Also z.B. aus dunkelgrün überall hellgrün machen?
Das bekommst Du ohne VBA hin, wenn Du mit der bedingten Formatierung z.B. anhand von Zahlenwerten Farben setzt. Allerdings musst Du Dich dann entweder auf eine geringe Anzahl beschränken oder Du bekommst reichlich Regeln in der bF.

Im Prinzip mit einem Zellinhalt oder einer Formel
Zahl=1; hellgrün
Zahl=2; dunkelgrün
usw.

Die Striche würde ich wie Elex auch so setzen:
   

also im Prinzip
=WIEDERHOLEN(" ";B1-1)&"|"

Ob man dann einen langen String wie bei Elex nimmt oder per WENN entscheidet, in welcher der 4 Zellen die Markierung stehen soll, wäre sicher beides möglich.
Du darfst dabei eben nur nicht die Zellbreiten verändern - also z.B. sicherheitshalber dann den Blattschutz setzen dass es nicht andere tun

Ich kenne was in der Art wie vom Bastler auch. Da wird aber eine bestimmte Anzahl Zeichen für den Bereich definiert, z.B. 4, und da steht dann jedes Zeichen für eine Ausschöpfung von 25% des jeweiligen Bereichs.

Wäre nur festzulegen, wie bei Rot vorzugehen ist. Eine Überschreitung ist ja zuweilen ziemlich weit möglich, kann also deutlich außerhalb des durch die Zellbreite eingeengten Bereichs liegen.