Bedingte Formatierung mit VBA

Hallo,
ich verwende aktuell die Bedingte Formatierung per Formel. Da sich die betroffene Zellenanzahl ständig erhöht, steigt die Dateigröße stark an.
Um dem entgegenzuwirken versuche ich die Bedingte Formatierung per VBA zu lösen. Mit der Aufzeichnung des Makrorecorders ist dies mir auch grundsätzlich bereits gelungen. Wie ich dieses Makro aber für eine Vielzahl von Zellen ausweiten kann ist mir jetzt unklar.
Beschreibung:
Spalte ALQ3 bis ALQ1001: Zahlen von 9 bis 23 in unsortierter Reihenfolge
Spalte ANA3 bis ANA17: Zahlen pro Zeile ab 9 bis 23 aufsteigend
Spalte ANB3 bsi ANB17: unterschiedliche Zahlen pro Zeile
Spalte ALM3 bis ALM1001: Ergebniszahlen die alle auch in der Spalte ANB3 bis ANB17 vorkommen
Für die Spalte ALM muss die bedingte Formatierung per SVERWEIS erstellt werden. Dabei werden jeweils vier unterschiedliche Farbtöne erstellt; hier beispielhaft für Zelle ALM3:
Farbton1: "=ALM3=SVERWEIS($ALQ3;$ANA$3:$ANB$17;2;0)"
Farbton2: "=ALM3=(SVERWEIS($ALQ3;$ANA$3:$ANB$17;2;0)+1)"
Farbton3: "=ALM3=(SVERWEIS($ALQ3;$ANA$3:$ANB$17;2;0)+2)"
Farbton4: "=ALM3>(SVERWEIS($ALQ3;$ANA$3:$ANB$17;2;0)+2)"
Diese Formatierung ist nach unten also ALM4, ALM5 usw., derzeit bis Zeile100
und nach links, also ALL3, ALL4, ALL5 usw., derzeit bis Spalte ALA und Zeile 100
zu übertragen.
Mein Aufzeichnungsmakro für die Zelle ALM3 mit manueller Erweiterung für die Zellen ALM4 und ALM5 sieht so aus:

Sub bedform1()
' bedform1 Makro
''Zeile3
Range("ALM3").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=ALM3=SVERWEIS($ALQ3;$ANA$3:$ANB$17;2;0)"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent1
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Range("ALM3").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=ALM3=(SVERWEIS($ALQ3;$ANA$3:$ANB$17;2;0)+1)"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent1
.TintAndShade = 0.5
End With
Selection.FormatConditions(1).StopIfTrue = False
Range("ALM3").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=ALM3=(SVERWEIS($ALQ3;$ANA$3:$ANB$17;2;0)+2)"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent1
.TintAndShade = 0.9
End With
Selection.FormatConditions(1).StopIfTrue = False
Range("ALM3").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=ALM3>(SVERWEIS($ALQ3;$ANA$3:$ANB$17;2;0)+2)"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent1
.TintAndShade = -0.1
End With
Selection.FormatConditions(1).StopIfTrue = False
'' Zeile4
Range("ALM4").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=ALM4=SVERWEIS($ALQ4;$ANA$3:$ANB$17;2;0)"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent1
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Range("ALM4").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=ALM4=(SVERWEIS($ALQ4;$ANA$3:$ANB$17;2;0)+1)"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent1
.TintAndShade = 0.5
End With
Selection.FormatConditions(1).StopIfTrue = False
Range("ALM4").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=ALM4=(SVERWEIS($ALQ4;$ANA$3:$ANB$17;2;0)+2)"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent1
.TintAndShade = 0.9
End With
Selection.FormatConditions(1).StopIfTrue = False
Range("ALM4").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=ALM4>(SVERWEIS($ALQ4;$ANA$3:$ANB$17;2;0)+2)"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent1
.TintAndShade = -0.1
End With
Selection.FormatConditions(1).StopIfTrue = False
'' Zeile5
Range("ALM5").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=ALM5=SVERWEIS($ALQ5;$ANA$3:$ANB$17;2;0)"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent1
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Range("ALM5").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=ALM5=(SVERWEIS($ALQ5;$ANA$3:$ANB$17;2;0)+1)"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent1
.TintAndShade = 0.5
End With
Selection.FormatConditions(1).StopIfTrue = False
Range("ALM5").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=ALM5=(SVERWEIS($ALQ5;$ANA$3:$ANB$17;2;0)+2)"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent1
.TintAndShade = 0.9
End With
Selection.FormatConditions(1).StopIfTrue = False
Range("ALM5").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=ALM5>(SVERWEIS($ALQ5;$ANA$3:$ANB$17;2;0)+2)"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent1
.TintAndShade = -0.1
End With
Selection.FormatConditions(1).StopIfTrue = False
End Sub

Wie muss der Code angepasst werden, dass er für die oben beschriebenen Bereiche (also von ALA3 bis ALM100) die bedingte Formatierung anzeigt?
Vielen Dank für eine Hilfe.
mfg

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

Hallo Erich,
... ist natürlich auch möglich, jedoch nur über eine im Namensmanager zugewiesene benannte Formel. Dies zu tun, macht auch Sinn, weil damit die Datenstruktur innerhalb der Tabellenblätter unabhängig von Formeländerungen gestaltet werden kann, vorausgesetzt natürlich, diese werden korrekt definiert.
Ein weiterer Vorteil dessen ist auch, dass Du die (hier im Beispiel: drei) von einander abhängigen Tabellen beliebig innerhalb des Tabellenblattes verschieben kannst, ohne Änderungen an den Formeln und Bezügen vornehmen zu müssen. Dies hab ich Dir in beigefügter Datei bereits aufgezeigt. Kannst es natürlich dort auch nachvollziehen & testen.
Nun noch der Hinweis darauf, dass die Auswertung in Deinem Beispiel dadurch noch etwas beschleunigt werden kann, indem ich hier das 4. Argument der SVERWEIS()-Formel inklusive dessen vorangestellten Semikolons wegfallen lassen habe. Dies ist hier möglich, weil Deine MAX-Werte der Dat_MAX Tabelle aufwärts sortiert sind.
https://www.herber.de/bbs/user/131007.xlsx
Gruß Werner
.. , - ...

Beliebteste Forumthreads (12 Monate)

9
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

326 Forumthreads zu ähnlichen Themen

Beliebteste Forumthreads (12 Monate)

Beliebteste Forumthreads (12 Monate)

Bedingte Formatierung mit VBA

9 Beiträge zum Forumthread Beiträge zu diesem Forumthread

326 Forumthreads zu ähnlichen Themen

VBA Beispiele zu verwandten Themen

Beliebteste Forumthreads (12 Monate)

Beliebteste Forumthreads (12 Monate)

9
Beiträge zum Forumthread

Beiträge zu diesem Forumthread