Microsoft Excel

Herbers Excel/VBA-Archiv

Anzahl bedingter Formatierungen auslesen

Betrifft: Anzahl bedingter Formatierungen auslesen von: Jürgen
Geschrieben am: 07.10.2020 10:35:35

Hallo,

ich habe eine größere Tabelle mit ca. 60 Spalten und 5000 Datensätzen/ Zeilen. Mittels VBA habe ich auf bestimmte Felder diverse Validierungsregeln gemacht, die die Funktion bedingte Formatierung von Excel verwendet.

Dazu habe ich 2 Fragen:

1. Kann ich die Anzahl der Felder, die durch die bedingte Formatierung markiert werden auslesen und ausgeben? Wenn ja wie?

2. Wie kann ich in VBA einzelne Validierungen an und ausschalten, gesteuert über das Tabellenblatt. Beispielsweise steht auf dem Tabellenblatt der Name und dahinter ist dann einfach ein Ja/Nein Feld mit dem ich steuern könnte, ob die Validierung durchlaufen wird oder nicht.


Ausschnitte aus dem Makro sehen so aus. Wenn das Makro ausgeführt wird werden zunächst alle _ bedingten Formatierungen gelöscht. Danach die einzelnen Validierungsregeln durchlaufen.

Sub ValidationRules()

' Delete all conditional formats on sheet Validation Sheet
    Sheets("Validation Sheet").Cells.FormatConditions.Delete
' Switch to Validation Sheet
Sheets("C14.00 Validation Sheet").Select

' Validation Rule
' v7366
'
    Range("ae3:ae5000").Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=AND(ae3<>"""";ah3<>ad3+ae3)"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False

' Validation Rule
' v7675
'
    Range("F3:F5000").Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=ISNA(MATCH(F3;LookupTables!$C$7:$C$9;0))"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
'...

End Sub


Hat jemand eine Idee und kann mir helfen?


Danke und viele Grüße

Jürgen

Betrifft: Crosspost ohne Hinweis
von: Werner
Geschrieben am: 07.10.2020 10:38:12

Hallo,

wie wäre es, wenn du die Beiträge in den verschiedenen Foren untereinander verlinkst?

Gruß Werner

Betrifft: AW: Anzahl bedingter Formatierungen auslesen
von: Jürgen
Geschrieben am: 07.10.2020 10:46:00

Auch hier eingestellt: https://www.vba-forum.de/View.aspx?ziel=62189-Anzahl_bedingter_Formatierungen_mit_vba_auslesen

Betrifft: AW: Anzahl bedingter Formatierungen auslesen
von: Yal
Geschrieben am: 07.10.2020 11:25:45

Hallo Jürgen,

fraglich ist, welche Ziel Du mit diesen bedingten Formatierung verfolgst?
Wäre es nicht sinnvoller, ein Filter einzusetzen? Bei Bedarf mit einer Hilfspalte.
In einer weiteren Spalte kommt diese Ja/Nein rein und kann auch mitgefiltert werden.

VG
Yal

Betrifft: AW: Anzahl bedingter Formatierungen auslesen
von: Jürgen
Geschrieben am: 07.10.2020 11:39:50

Hi Yal,
mit der bedingten Formatierung markiere ich die Validierungsfehler im Tabellenblatt bzw. die Felder in denen der Fehler auftritt. Es sind ca. 80 Regeln, die ich so aufgesetzt habe. zusätzlich würde ich eben gerne die Anzahl der Fehler pro Regel berechnen und ausgeben und zusätzlich die Möglichkeit Regeln an und auszuschalten.

Viele Grüße
Jürgen

Betrifft: AW: Anzahl bedingter Formatierungen auslesen
von: Yal
Geschrieben am: 07.10.2020 11:49:59

dann 80 Hilfespalten... Die kann man anschliesend leichter löschen als bedingte Formatierungen. Mit VBA bringst Du oft deine Daten in einem schwer umkehrbaren Zustand.

Beim Filter sieht man sofort wieviel Datensätze selektiert worden sind.
Notfalls kann man auch komplexe Auswertung Mithilfe Pivottabelle machen.

VG Yal

Betrifft: AW: Anzahl bedingter Formatierungen auslesen
von: Jürgen
Geschrieben am: 07.10.2020 12:06:32

ok Danke für die Rückmeldung. Die Hilfsspalten wären eine Möglichkeit. Aber hast Du auch eine Idee wie ich die beiden Punkte in vba lösen kann?

VG
Jürgen

Betrifft: AW: Anzahl bedingter Formatierungen auslesen
von: Yal
Geschrieben am: 07.10.2020 13:49:17

da müsste ich mich reinarbeiten: wie ist der Datenmodell, worauf kann man greifen, usw....
Im Einzelschritt-Modus die Objekte im Lokalfenster anschauen.

Der Makro Recorder sagt mir, dass das Selektieren der Zellen mit bedingten Formatierung wie folgt lautet:
ActiveSheet.Cells.SpecialCells(xlCellTypeAllFormatConditions).Select

Dann würde ich zuerst diese Zellen durchlaufen und der Format im Direktfenster rausgeben.

Da das Objekt FormatConditions an das Auflistungsobjekt Cells angehängt ist, vielleicht lieber
For Each FC in ActiveSheet.FormatConditions
    Debug.Print FC.AppliesTo
Next
usw.

Viel Erfolg
Yal