VBA - Bedingte Formatierung flexibel gestalten

[EasY]

Hi Leute,

ich stehe bei folgendem Problem auf dem Schlauch. Ich möchte meine bedingte Formatierung etwas flexibel gestalten. Dazu gibt es eine "Legende" auf dem Blatt Einstellungen. Das Makro soll nun für den Zellbereich des Blattes Januar, welcher in A1 steht, die bedingten Formatierungen übernehmen.

In meiner Beispieldatei sollen die Texte die ich eingefügt habe mit der entsprechenden Farbe hinterlegt werden. Ich denke, dass die Datei mehr Aussagekaft hat als mein Text hier :)


Gruß

[DeBabba]

Hi EasY
schau doch mal in die Anlage,
So gehts

  Flexible bedingte FormatierungLösung.xlsm (Größe: 18,53 KB / Downloads: 5)

Gruß
DeBabba

[EasY]

Hi DeBabba,

danke für deinen Versuch. Es ging mir aber eher um die Programmierung des vorhandenen Makros. Deswegen habe ich auch "VBA" im Betreff geschrieben. Das manuelle Hinzufügen einer bedingten Formatierung ist nicht das Problem.

[DeBabba]

Sorry, war wohl schon ein wenig spät.
Ich nehme gerade für die bedingte Formatierung erst die manuelle Lösung, dann den Recorder und passe dann z.B. Bereiche etc. entsprechend an
Statt Select --> einen Bereich generieren Set Bereich = Range("A1:G100"), diesen dann statt Selection --> Bereich.  usw. usw.



Gruß
Klaus

[EasY]

Naja das hilft mir wenig weiter... Vielleicht hat jemand anderes eine Idee? 

Im Wesentlichen habe ich zwei Probleme: 

1. Wie im Makro angedeutet, soll in der eigentlichen Datei nicht nur der Januar bedient werden sondern die anderen Monate auch. Deshalb habe ich eine For-Next-Schleife drin. Nun ist nicht jedes Blatt identisch sodass der Bereich, in dem die bedingten Formatierungen geschrieben werden, jeweils in Zelle A1 steht. In meinem Makro übernimmt er leider nicht den Inhalt sondern schreibt die Formatierungen nur in die Zelle A1.

2. Es sollen nicht nur eine bedingte Formatierung geschrieben werden sondern mehrere. Allerdings schreibt mein Makro ja die Formel, für die bedingte Formatierung gilt, fest in die bedingte Formatierung. Wie kann ich auch diese Formel variabel schreiben, sodass ich ebenso eine for-next-schleife drumherum basteln kann? 

Gruß

[Elex]

Hi

Hier mal bischen Spielerrei. Schau ob es dir weiter hilft.

  Flexible bedingte Formatierung.xlsm (Größe: 25,02 KB / Downloads: 4)

Gruß Elex

[EasY]

Hi Elex,

das sieht sehr sehr gut aus :) ... Ich würde es nur gerne verstehen, damit ich weiter lerne - vll kannst du mir dabei nochmal helfen:

Code:

For j = 2 To Sheets.Count
 With Sheets(j).Cells
    For jj = .FormatConditions.Count To 1 Step -1
      If InStr(1, .FormatConditions(jj).Formula1, "Einstellungen") Then .FormatConditions(jj).Delete
    Next jj
 End With
Next j

Ich würde es Zeile für Zeile gerne durchgehen :) ..

1. Der Code gilt für Blatt 2 (Januar) bis Anzahl der Blätter
2. Die nächste Zeile, da liest er die Anzahl der bedingten Formatierungen des Blattes? Oder was bewirkt die Zeile?
3. Er schaut, ob er in der Formel der bedingten Formatierung den String "Einstellungen" findet und löscht diese bed. Formatierung dann?
4 und Rest. Selbsterklärend

Code:

Set rngA = Sheets("Einstellungen").Range(Sheets("Einstellungen").Range("A1").Value2)

For j = 2 To Sheets.Count
Set rngB = Sheets(j).Range(Sheets(j).Range("A1").Value2)
  For jj = 1 To rngA.Rows.Count
      Fstr = "=ZÄHLENWENN(Einstellungen!" & rngA.Rows(jj).Address & ";" & rngB.Cells(1).Address(0, 0) & ")*(" & rngB.Cells(1).Address(0, 0) & "<>"""")"
      rngB.FormatConditions.Add Type:=xlExpression, Formula1:=Fstr
      rngB.FormatConditions(jj).StopIfTrue = False
      rngB.FormatConditions(jj).Interior.Color = rngA.Columns(1).Cells(jj).Offset(, -2).Interior.Color
  Next jj
Next j

1. Ich denke ich benötige diese Range nicht, da die Legende ausreichend dimensioniert ist, dass sie fest bleibt :)
2. Das hatten wir schon
3. Da sah mein kläglicher Versuch, den Bereich darzustellen, wirklich Banane gegen aus. Ich verstehe nur nicht, wieso value2 anstatt value?
4. Makro geht Spalte nach Spalte, je nachdem wieviele Spalten die Range in A1 hat.
5. Puh da steige ich nicht durch
6. Die bedingte Formatierung (Fstr) wird in den Bereich rngB geschrieben?
7. Was passiert hierdurch?
8. Hier schreibt er die Hintergrundfarbe, welche die Zelle hat, die zwei Zeilen links der ersten Zelle des Bereichs rngA ist.

Rest ist selbsterklärend.


Auf jeden Fall sehr sehr schick programmiert, wenn ich das mal so als Laie sagen darf :)


Gruß


P.S.: Was mir noch aufgefallen ist und zu Problemen bei meiner Datei kommen könnte: Die jeweilige Zelle A1 in den Tabellenblättern ist flexibel. Deswegen steht dort auch "=B3:C9" und nicht "B3:C9", sodass bei Hinzufügen einer neuen Spalte der Bereich automatisch in "B3:D9"  angepasst wird. Kann man das noch irgendwie beachten oder wirft das nun Probleme auf sodass ich es anders lösen muss?

[Elex]

Hi

Teil 1:
1.  Ja
2.  Startbedingungen für Schleife.
      Zähle Rückwärts von Gesamtanzahl Bed.Formate in dem Sheet bis 1.
     (Bei Delete Schleifen (z.B. auch Löschen von Zeilen)  immer Rückwärts zählen)
3.  Ja. Ob das in der Form nötig ist? Wenn es keine weiteren Formate gibt, kannst du
     auch gleich alle in einem Rutsch löschen.
 
Teil 2:
1. Klar kannst du die auch fest angeben. Set rngA = Sheets(„Ein…“).Range(„K5:M8“)
3. Value oder Value2 ist hier egal. Value2 soll schneller sein, habe ich irgend wo gelesen. Und da macht man das doch. :05:
     Wenn du in A1 es in der Form =B8:H13 schreibst, kannst du es so Auslesen. Siehe neuen Code.
     Set rngB = Sheets(j).Range(Replace(Sheets(j).Cells(1).FormulaLocal, "=", ""))
4.  Zeile für Zeile
5.  Hier wird der Formelstring für die Bed.Formatierung zusammengebaut. Schau dir den Inhalt der Variable(Fstr) an.
6.  Ja
7.  Ist  in der Hilfe erklärt.  „StopIfTrue“   Setzt den Haken bei Anhalten oder eben keinen.
8.  Zwei Spalten links neben aktuell verarbeiteter Zeile.
 
 
Solltest du in deiner Datei wirklich auch noch andere Bed.Formate verwenden, die nicht durch den Code vergeben werden, solltest du den etwas geänderten Code verwenden.

Code:

Sub Legende_Übernehmen()
Dim j As Long, BdF, rngA As Range, rngB As Range, Fstr As String
Dim jj As Long

'alle Formatierungen die mit dem Blatt "Einstellungen" Zusammenhängen löschen
For j = 2 To Sheets.Count
  With Sheets(j).Cells
     For jj = .FormatConditions.Count To 1 Step -1
       If .FormatConditions(jj).Type = xlExpression Then _
         If InStr(1, .FormatConditions(jj).Formula1, "Einstellungen") Then .FormatConditions(jj).Delete
     Next jj
  End With
Next j

'neue Formatierung anlegen
Set rngA = Sheets("Einstellungen").Range("K5:M8")

For j = 2 To Sheets.Count
 Set rngB = Sheets(j).Range(Replace(Sheets(j).Cells(1).FormulaLocal, "=", ""))
   For jj = 1 To rngA.Rows.Count
       Fstr = "=ZÄHLENWENN(Einstellungen!" & rngA.Rows(jj).Address & ";" & rngB.Cells(1).Address(0, 0) & ")*(" & rngB.Cells(1).Address(0, 0) & "<>"""")"
       rngB.FormatConditions.Add Type:=xlExpression, Formula1:=Fstr
       rngB.FormatConditions(rngB.FormatConditions.Count).SetFirstPriority
       rngB.FormatConditions(1).StopIfTrue = False
       rngB.FormatConditions(1).Interior.Color = rngA.Columns(1).Cells(jj).Offset(, -2).Interior.Color
   Next jj
Next j
End Sub

Bei Fragen einfach Melden.

Gruß Elex