Live-Forum - Die aktuellen Beiträge
Datum
Titel
18.04.2024 21:09:11
4 Spalten aus smarter Tabelle in andere smarte Tab. kopieren
18.04.2024 20:45:35
Worksheet_Activate für alle Sheets
18.04.2024 19:30:20
VBA - Tabelle mit Mannschaftsergebnissen sortieren
18.04.2024 18:04:29
Formel fehler Wert#
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1276to1280
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1276to1280
1276to1280
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Farbe per Formel übernehmen

Farbe per Formel übernehmen
Tom

Hallo Excelprofis,
stehe vor folgendem Problem:
in der beiliegenden Beispieldatei habe ich im Tabellenblatt "Stammdaten" eine Liste mit Ländern definiert, wobei jedes Land mit einer separaten Farbe gekennzeichnet ist.
Nun möchte ich im Tabellenblatt "Übersicht" über ein Drop-Down-Feld das passende Land auswählen (Gültigkeit - Liste). Dabei soll aber nicht nur die Bezeichnung des Landes übernommen werden, sondern auch die im Tabellenblatt "Stammdaten" beim jeweils selektierten Land gewählte Farbe.
Falls im Tabellenblatt "Stammdaten" die Farbe geändert wird, sollte dies im Tabellenblatt "Übersicht" beim ausgewählten Land automatisch geändert werden.
Ist so etwas überhaupt möglich? VBA-Kenntnisse habe ich zwar nur bescheidene (Recorder), ohne VBA wirds aber vermutlich nicht gehen.
Beispieldatei:
https://www.herber.de/bbs/user/81529.xlsx
Vielen Dank für eure Bemühungen!
Tom

AW: Farbe per Formel übernehmen
22.08.2012 20:31:26
Josef

Hallo Tom,
das geht nur per VBA.
In das Modul von "Übersicht"
' **********************************************************************
' Modul: Tabelle2  Typ: Element der Mappe(Sheet, Workbook, ...)
' **********************************************************************

Option Explicit

Private Sub Worksheet_Activate()
  Dim vntRet As Variant
  
  vntRet = Application.Match(Range("B2"), Sheets("Stammdaten").Range("Land"), 0)
  
  If IsNumeric(vntRet) Then
    Range("B2").Interior.Color = Sheets("Stammdaten").Range("Land").Cells(vntRet, 1).Interior.Color
  Else
    Range("B2").Interior.ColorIndex = xlNone
  End If
  
End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
  Dim vntRet As Variant
  If Target.Address(0, 0) = "B2" Then
    vntRet = Application.Match(Target, Sheets("Stammdaten").Range("Land"), 0)
    
    If IsNumeric(vntRet) Then
      Target.Interior.Color = Sheets("Stammdaten").Range("Land").Cells(vntRet, 1).Interior.Color
    Else
      Target.Interior.ColorIndex = xlNone
    End If
  End If
  
End Sub



« Gruß Sepp »

Anzeige
AW: Farbe per Formel übernehmen
22.08.2012 20:45:21
Tom
Hallo Sepp!
Hut ab, funktioniert ausgezeichnet, eine tolle Lösung! Vielen Dank!
Eine Frage:
wäre es auch möglich, wenn man nun die Zelle B2 an verschiedene Stellen im Tabellenblatt Übersicht kopiert, dort neben der Gültigkeit auch die per VBA gesteuerte "bedingte Formatierung" mitzukopieren?
Bspw. wenn man B2 mit Strg+C auf C8 kopiert, dass dort die gleiche Funktionalität besteht wie in Zelle B2?
Falls zu schwierig, kein Problem, wäre halt das "Tüpfelchen auf dem I".
Vielen Dank und schöne Grüße
Tom

AW: Farbe per Formel übernehmen
22.08.2012 20:53:08
Josef

Hallo Tom,
geht auch. Gilt für alle Zellen die für die Liste auf "Land" zugreifen.
' **********************************************************************
' Modul: Tabelle2  Typ: Element der Mappe(Sheet, Workbook, ...)
' **********************************************************************

Option Explicit

Private Sub Worksheet_Activate()
  Dim rng As Range, rngV As Range
  Dim vntRet As Variant
  
  On Error Resume Next
  Set rngV = Me.UsedRange.SpecialCells(xlCellTypeAllValidation)
  On Error GoTo 0
  
  If Not rngV Is Nothing Then
    For Each rng In rngV
      If rng.Validation.Formula1 = "=Land" Then
        vntRet = Application.Match(rng, Sheets("Stammdaten").Range("Land"), 0)
        
        If IsNumeric(vntRet) Then
          rng.Interior.Color = Sheets("Stammdaten").Range("Land").Cells(vntRet, 1).Interior.Color
        Else
          rng.Interior.ColorIndex = xlNone
        End If
      End If
    Next
  End If
  
  Set rng = Nothing
  Set rngV = Nothing
End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
  Dim vntRet As Variant
  If Not Target(1, 1).Validation Is Nothing Then
    If Target.Validation.Formula1 = "=Land" Then
      vntRet = Application.Match(Target, Sheets("Stammdaten").Range("Land"), 0)
      
      If IsNumeric(vntRet) Then
        Target.Interior.Color = Sheets("Stammdaten").Range("Land").Cells(vntRet, 1).Interior.Color
      Else
        Target.Interior.ColorIndex = xlNone
      End If
    End If
  End If
  
End Sub



« Gruß Sepp »

Anzeige
AW: Farbe per Formel übernehmen
22.08.2012 22:11:22
Tom
Hallo Sepp,
vielen Dank, auch das funktioniert beim Kopieren mit Strg+C super (wo lernt man dieses VBA-Coding :-) ?), unglaublich, was man hier alles machen kann.
Allerdings habe ich nun das Problem, dass, wenn ich an x-beliebiger Stelle im Tabellenblatt "Übersicht" eine Eingabe machen, ein Laufzeitfehler ausgegeben wird (das Kopieren der Zelle mit Gültigkeit und bedingter Formatierung funktioniert aber).
Beim Debuggen bleibt die Ausführung an der Anweiung If Target.Validation.Formula1 = "=Land" Then
stehen.
Beispieldatei ist angeführt
https://www.herber.de/bbs/user/81530.xlsm
Habe jetzt selber schon eine Zeit lang versucht, woran das liegen könnte, komme aber nicht drauf
(meine VBA-Kenntnisse sind wie erwähnt beinahe gleich 0)
Wäre super, wenn du mal drüber schauen könntest
Vielen Dank
Tom

Anzeige
AW: Farbe per Formel übernehmen
22.08.2012 22:29:30
Josef

Hallo Tom,
zu wenig getestet;-((
Aber so läufts;-))
' **********************************************************************
' Modul: Tabelle2  Typ: Element der Mappe(Sheet, Workbook, ...)
' **********************************************************************

Option Explicit

Private Sub Worksheet_Activate()
  Dim rng As Range, rngV As Range
  Dim vntRet As Variant
  
  On Error Resume Next
  Set rngV = Me.UsedRange.SpecialCells(xlCellTypeAllValidation)
  On Error GoTo 0
  
  If Not rngV Is Nothing Then
    For Each rng In rngV
      If rng.Validation.Formula1 = "=Land" Then
        vntRet = Application.Match(rng, Sheets("Stammdaten").Range("Land"), 0)
        
        If IsNumeric(vntRet) Then
          rng.Interior.Color = Sheets("Stammdaten").Range("Land").Cells(vntRet, 1).Interior.Color
        Else
          rng.Interior.ColorIndex = xlNone
        End If
      End If
    Next
  End If
  
  Set rng = Nothing
  Set rngV = Nothing
End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
  Dim rng As Range
  Dim vntRet As Variant
  
  For Each rng In Target
    If HasValidation(rng) Then
      If rng.Validation.Formula1 = "=Land" Then
        vntRet = Application.Match(rng, Sheets("Stammdaten").Range("Land"), 0)
        
        If IsNumeric(vntRet) Then
          rng.Interior.Color = Sheets("Stammdaten").Range("Land").Cells(vntRet, 1).Interior.Color
        Else
          rng.Interior.ColorIndex = xlNone
        End If
      End If
    End If
  Next
  
End Sub


Private Function HasValidation(Target As Range) As Boolean
  On Error Resume Next
  HasValidation = Target.Validation.Type > -1
End Function



« Gruß Sepp »

Anzeige
AW: Farbe per Formel übernehmen
23.08.2012 07:37:34
Tom
Hallo Sepp,
vielen herzlichen Dank! Nun funktioniert alles einwandfrei, wirklich eine tolle Lösung!
Schade, dass ich das nicht selber kann :-)
Wird Zeit für einen VBA-Kurs...
Mit Gruß
Tom

AW: Farbe per Formel übernehmen
23.08.2012 08:08:12
Tom
Hallo Sepp,
nun noch eine (hoffentlich) letzte Frage:
wenn ich nun bspw. in B2 per Dropdown das jeweilige Land auswähle, wird ja die Farbe nun richtig übernommen.
in B3 steht nun ein weiteres Dropdown-Feld mit der selben Listenauswahl, allerdings wird zunächst als Vorschlag per Formel auf B2 referenziert.
Das heißt, dass der Anwender zunächst in B3 als Vorschlagswert den Inhalt von B2 vorgeschlagen bekommt, diesen aber durch Auswahl des entsprechenden Listeneintrages in B3 überschreiben kann.
Wird nun B2 abgeändert, so ändert sich auch der Wert-Inhalt in B3, das Formal wird allerdings erst bei aktiver Auswahl des Listeneintrages in B3 über das Dropdownfeld wird umformatiert.
Gäbe es eine Möglichkeit, dass, wenn in B2 ein anderer Listeneintrag ausgewählt wird (und somit die Farbe umgestellt wird) das gültige Format auch auf B3 übernommen wird (die Inhalte werden ja durch den Formelbezug bereits richtig übertragen).
Falls zu aufwändig, kein Problem, die Lösung funktioniert ja schon ausgezeichnet, wäre nur ein nice to have.
Vielen Dank
Tom

Anzeige
AW: Farbe per Formel übernehmen
23.08.2012 12:02:35
Josef

Hallo Tom,
lade das Beispiel mit der Formelverknüpfung hoch.

« Gruß Sepp »

AW: Farbe per Formel übernehmen
23.08.2012 13:50:28
Tom
Hallo Sepp,
hier die Beispieldatei.

Die Datei https://www.herber.de/bbs/user/81534.xlsm wurde aus Datenschutzgründen gelöscht


Vielen Dank für deine Bemühungen!
Mit Gruß
Tom

Anzeige
AW: Farbe per Formel übernehmen
23.08.2012 14:57:01
Josef

Hallo Tom,
probier mal, die Zellen werden automatisch erkannt, auch wenn du zusätzliche Zeilen benötigst.
https://www.herber.de/bbs/user/81535.xlsm

« Gruß Sepp »

AW: Farbe per Formel übernehmen
23.08.2012 17:59:09
Tom
Hallo Sepp,
vielen herzlichen Dank, super Service!!! Genauso, wie ich es mir vorgestellt habe, nun klappt es perfekt, wenngleich ich den Code nicht verstehe :-).
Vielen Dank nochmals und beste Grüße
Tom

Anzeige
AW: Farbe per Formel übernehmen
23.08.2012 08:13:25
Tom
Hallo Sepp,
nun noch eine (hoffentlich) letzte Frage:
wenn ich nun bspw. in B2 per Dropdown das jeweilige Land auswähle, wird ja die Farbe nun richtig übernommen.
in B3 steht nun ein weiteres Dropdown-Feld mit der selben Listenauswahl, allerdings wird zunächst als Vorschlag per Formel auf B2 referenziert.
Das heißt, dass der Anwender zunächst in B3 als Vorschlagswert den Inhalt von B2 vorgeschlagen bekommt, diesen aber durch Auswahl des entsprechenden Listeneintrages in B3 überschreiben kann.
Wird nun B2 abgeändert, so ändert sich auch der Wert-Inhalt in B3, das Formal wird allerdings erst bei aktiver Auswahl des Listeneintrages in B3 über das Dropdownfeld wird umformatiert.
Gäbe es eine Möglichkeit, dass, wenn in B2 ein anderer Listeneintrag ausgewählt wird (und somit die Farbe umgestellt wird) das gültige Format auch auf B3 übernommen wird (die Inhalte werden ja durch den Formelbezug bereits richtig übertragen).
Falls zu aufwändig, kein Problem, die Lösung funktioniert ja schon ausgezeichnet, wäre nur ein nice to have.
Vielen Dank
Tom
Anzeige

215 Forumthreads zu ähnlichen Themen

Textformatierung in einer Formel
Dieterlem

Hallo Excelfreunde,
ich möchte ein Wort fett innerhalb eines Formelergebnisses fett darstellen. Beispiel:
"WENN(A1="Ja";"Beispieltext soll Fett dargestellt werden";"")
Dabei soll nur das Wort 'Fett', fett formatiert werden.
Ist so etwas möglich?
Gruß
Dieterlem
27.10.2008 19:55:00
Thread-ID: 1019731
3 Beiträge
Anzeige
Zellen formatierung mit Formeln
Peter

Hallo
habe eine Formatierung in einer Zelle, in der er die 0 als 00 anzeigen soll.
Das macht er so lange ich keine Formel drin habe.
Sobald die Formel drin ist., zeigt er mir nur eine 0 (Null an.
die Formel ist
RECHTS(SUMMEWENN(C14:C40;">0";G14:G40);2)
Wie bekomme ic...
08.11.2008 18:25:00
Thread-ID: 1022970
7 Beiträge
Formatierung mit in die Formel einarbeiten
Jochen

Hallo,
habe folgende Formel:
=WENN(A8"";VERKETTEN(SVERWEIS(A8;B2:E5;2;FALSCH);ZEICHEN(10);SVERWEIS(A8;B2:E5;3;FALSCH);" / "; SVERWEIS(A8;B2:E5;4;FALSCH));"") .
Diese Formel verkettet "Name" - "Vorname" - "/" und eine weitere "Bezeichnung" miteinander.
Die Zelle ist Fett und Schr...
12.01.2009 10:57:00
Thread-ID: 1038800
4 Beiträge
Anzeige
Formatieren mit Formel
hans

Hallo zusammen
in F2 steht eine Zahl von 0 bis 23, sollen für die Stunden des Tages stehen..
jetzt habe ich in einer anderen Zelle folgende Formel:
=F2&".00 - "&F2 &".59"
dies gibt den zum Beispiel bei "F2 = 16" das Resultat "16.00 - 16.59"
wenn jetzt aber F...
13.05.2009 10:51:40
Thread-ID: 1074745
6 Beiträge
Formel für bedingte Formatierung
Ernst

Hallo Vba Experten !
Ich habe ein Problem und zwar habe in Range A8:A105 Schriftfarbe rot ich möchte nun wenn die jeweillige Nachbarzelle gefüllt ist das sich die Schriftfarbe auf grün Ändert.
wenn ich das einzeln mache funktioniert es einwandfrei. jedoch habe ich Probleme wenn ich da...
05.06.2009 12:40:30
Thread-ID: 1079430
6 Beiträge
Formel für bedingte Formatierung ?
Ernst

Hallo Vba Profis !
Hätte da wieder mal ein Frage und zwar "wie müsste eine Formel aussehen ?, sobald in Spalte A Range(A:6,A300) eine Zelle Aktiv ist diese zb.mit grünem Hindergrund dargestellt wird". bem verlassen der Zelle sollte die jeweils ursprüngliche Farbe wiederhergestellt werden.Be...
17.06.2009 07:31:04
Thread-ID: 1082111
3 Beiträge
Bedingte Formatierung mit Formel ist
Wolfgang

Hallo Leute
Ich hoffe es kann mir jemand einen Tip geben, Ich wäre euch sehr dankbar
Ich möchte in einer Liste mit der Bedingten Formatierung immer Blöcke von 10 Zeilen farblich makieren.
Also 10 Zeilen Farblich, 10 Zellen nicht Farblich dann wieder 10 zeilen Farblich.
Um jede z...
18.06.2009 13:33:10
Thread-ID: 1082572
3 Beiträge
Formel in Bedinger Formatierung
Heinz

Hallo Leute
Ich bekomme leider folgende Formel in bedingter Formatierung nicht hin.
Wenn E5="Test" +K5 =0 ; Schriftfarbe grau
Könnte mir dazu bitte jemand helfen ?
Gruß
Heinz
30.06.2009 11:44:06
Thread-ID: 1085311
4 Beiträge
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Jetzt das Excel-Portal bewerten: Deine Stimme zählt!

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige
Archiv - Verwandte Themen
Forumthread
Beiträge
2
5
6
3
8
5
5
2
2
3
6
3
7
9
3
2
7
4
3
5
5
2
12
2
4