Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1588to1592
1588to1592
Aktuelles Verzeichnis
Verzeichnis Übersicht
Verzeichnis Übersicht
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Hilfe bei Bedingter Formatierung

Forumthread: Hilfe bei Bedingter Formatierung

Hilfe bei Bedingter Formatierung
10.11.2017 20:08:59
Markus
Hallo allerseits
Ich hoffe jemand kann mir weiterhelfen. Ich versuche eine Bedingte Formatierung per VBA zu erstellen aber es gelingt mir nur mit jeweils einem Bereich es sollten aber zwei sein.
Darum gehts:
Ich hab eine Tabelle in der es ca. 10 bedingte Formatierungen gibt. Da in diese Tabelle viel hineinkopiert wird und verschoben usw. entstehen dabei immer wieder neue bedingte Formatierungen. (Das macht die Datei langsamer.) Die ich dann immer wieder Löschen muss. Momentan sind es Zbsp. 140. aber 10 sollten es sein. Ich habe dort eine benutzerdefinierte Sortierung Per VBA drin und würde nun gerne bei jedem Sortieren alle bedingten Formatierungen Löschen und dann neu erstellen per VBA.
Zu der bedingten Formatierung
Es wird in Spalte A geschaut welcher Wert darin steht und dann wird in der betreffenden Zeile immer die spalte A und E bis N in einer Farbe eingefärbt B bis D bleiben weis.
So schauen die Bedingten Formatierungen aus:
1.Formel: = =$A3=1 Bereich: =A3: A (letzte Zeile +15); E3:M(letzte Zeile +15)
Zellenfarbe: Weis Schrift: Automatisch
2.Formel: =ODER($A3="RH";$A3="TT") Bereich: =A3: A (letzte Zeile +15); E3:M(letzte Zeile +15)
Zellenfarbe: Gelb Schrift: Rot
3.Formel: =UND($A3=1;$B3=40) Bereich: =A3: A (letzte Zeile +15); E3:M(letzte Zeile +15)
Zellenfarbe: Weis Schrift: Rot
4.Formel: =ODER($A3="2a";$A3="2b";$A3=2). Bereich: =A3: A (letzte Zeile +15); E3:M(letzte Zeile +15)
Zellenfarbe: Gelb Schrift: Automatisch
5.Formel: = $A3=3 Bereich: =A3: A (letzte Zeile +15); E3:M(letzte Zeile +15)
Zellenfarbe: Blau Schrift: Automatisch usw.
Ich hab google konnte es aber nicht richtig umsetzen. Wäre toll wenn mir jemand helfen könnt. Danke schonmal.
Grüße Markus
Anzeige

12
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Hilfe bei Bedingter Formatierung
10.11.2017 20:16:25
Markus
Das ist noch der Code der Sortierung die letzte Zeile wird da auch schon ermittelt und die alten Formatierungen gelöscht. Aber wie erstell ich die neuen. Wenn mir jemand ein Bsp. der ersten 4 erstellen könnte dann schaffe ich die restlichen 6 allein (glaub ich)
Mein derzeitiger Code: 
Sub Sortieren_Bedingteformatierung_Herstellen()
Dim x As Long
‘Letzte Benutzte zeile wird ermittelt über Funktion
x = LezteZ(Sheets("UP Datum"))
‘Sortierung für Tabelle
With ThisWorkbook.Worksheets("Tabelle 1").Sort
.SortFields.Clear
.SortFields.Add Key:=Range(Cells(5, 7), Cells(x, 7)), SortOn:=xlSortOnValues, Order: _
=xlAscending, DataOption:=xlSortNormal
.SortFields.Add Key:=Range(Cells(5, 5), Cells(x, 5)), SortOn:=xlSortOnValues, Order: _
=xlAscending, DataOption:=xlSortNormal
.SortFields.Add Key:=Range(Cells(5, 6), Cells(x, 6)), SortOn:=xlSortOnValues, Order: _
=xlAscending, DataOption:=xlSortNormal
'        .SortFields.Add Key:=Range(Cells(5, 1), Cells(X, 1), SortOn:=xlSortOnValues, Order: _
=xlAscending, DataOption:=xlSortNormal
.SetRange Range(Cells(2, 1), Cells(x, 17))
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
x = x + 15        ‘Erhöhung der letzten Zeile um 15 Zeilen
ThisWorkbook.AktiveSheet.FormatConditions.Delete  ‘Löschen aller alten Bedingten  _
Formatirungen
Range("A1").Select
End Sub

Anzeige
AW: Hilfe bei Bedingter Formatierung
10.11.2017 20:20:16
Markus
Sorry für den dritten Post hab beim zweiten den Hacken für die noch offene Frage vergessen und wusste nicht wie ich das nachträglich änder.
AW: Rekorder +++
10.11.2017 20:34:21
Fennek
Hallo,
für eine ähnliche Frage vor ein paar Tagen habe ich folgenden Code zuerst per Rekorder aufgezeichnet und dann etwas nachbearbeitet.
Die Anpassung für deine Fragestellung, schaffst du das? (um die Zeit jetzt werde ich es nicht machen)

Sub iFen()
Mo = Array(2, 16) 'für die anderen Monate ergänzen
For Each r In Mo
ls = Cells(r, Columns.Count).End(xlToLeft).Column
For j = 2 To ls
With Range(Cells(r, j), Cells(r + 10, j))
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=WOCHENTAG(" & Cells(r, j).Address & ";11) = 7"
.FormatConditions(.FormatConditions.Count).SetFirstPriority
With .FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 49407 'orange
.TintAndShade = 0
End With
.FormatConditions(1).StopIfTrue = False
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=WOCHENTAG(" & Cells(r, j).Address & ";11) = 1"
.FormatConditions(.FormatConditions.Count).SetFirstPriority
With .FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 255 'rot
.TintAndShade = 0
End With
.FormatConditions(1).StopIfTrue = False
End With
Next j
Next r
End Sub
mfg
Anzeige
AW: Rekorder +++
13.11.2017 08:29:17
Markus
Hallo,
also ich hab es versucht und nennen wir es einen Teilerfolg danke für das Code Beispiel. Versteh ich das richtig das die Zahl in der klammer von: .FormatConditions(1) die Nummer der Formatierung darstellt.
Diese Zeile Kapier ich auch nicht ganz: .FormatConditions(1).StopIfTrue = False. Schaltet das die Formatierung ab bzw. hält sie an? Ist es wichtig die Formatierung anzuhalten bevor die nächste geschrieben wird. Läuft das Programm dann schneller? Wie schalt ich dann Später alle auf einmal wider an?
Das ist mein derzeitiger Code. Ist der ansatz gut so oder gibt es eine bessere Möglichkeit? 
Private Sub cmdEinsortieren_in_Liste_Click()
Dim x As Long
x = LezteZ(Sheets("Tabelle 1")) ' Funktion ermitelt die letzte beschriebene Zeile
With ThisWorkbook.Worksheets("Tabelle 1").Sort
.SortFields.Clear
.SortFields.Add Key:=Range(Cells(5, 7), Cells(x, 7)), SortOn:=xlSortOnValues, Order: _
=xlAscending, DataOption:=xlSortNormal
.SortFields.Add Key:=Range(Cells(5, 5), Cells(x, 5)), SortOn:=xlSortOnValues, Order: _
=xlAscending, DataOption:=xlSortNormal
.SortFields.Add Key:=Range(Cells(5, 6), Cells(x, 6)), SortOn:=xlSortOnValues, Order: _
=xlAscending, DataOption:=xlSortNormal
'        .SortFields.Add Key:=Range(Cells(5, 1), Cells(X, 1), SortOn:=xlSortOnValues, Order: _
=xlAscending, DataOption:=xlSortNormal
.SetRange Range(Cells(2, 1), Cells(x, 17))
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
'--------- Formatierungs Code-----------
x = x + 15  'Letzte Zeil des zu formatierenden bereiches
Sheets("Tabelle1").Cells.FormatConditions.Delete
With Range(Cells(3, 1), Cells(x, 13))
.FormatConditions.Add Type:=xlExpression, Formula1:="=$A3=3"
.FormatConditions(.FormatConditions.Count).SetFirstPriority
With .FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 49407 'orange
.TintAndShade = 0
End With
'            .FormatConditions(1).StopIfTrue = False 'Was bewirkt diese zeile
.FormatConditions.Add Type:=xlExpression, Formula1:="=$A3=4"
With .FormatConditions(2).Interior
.PatternColorIndex = xlAutomatic
.Color = 255 'rot
.TintAndShade = 0
End With
'            .FormatConditions(1).StopIfTrue = False
End With
Range("A1").Select
End Sub
Es haben sich mir ein paar Fragen aufgeworfen villeicht hättest du kurz Zeit sie mir zu beantworten. Da wäre ich echt Dankbar dafür. Dann müsst glaub ich in der Lage sein den Code fertig zu stellen.
  • Wie kann ich zwei Bereiche angeben also =$A3:$A2000;$D3:$L2000 hab es mal so: Range((Cells(1, 1), Cells(x, 1));(Cells(3, 4), Cells(x, 13)))probiert aber so geht’s nicht.

  • Wie kann ich eine Formel eintragen die Buchstaben enthält? Die Buchstaben in einer Formel müssen ich ja in " " geschrieben werden aber in VBA kommt dann die Meldung Anweisungsende wird erwartet. bspw. .FormatConditions.Add Type:=xlExpression, Formula1:=:"=ODER($A3="RH";$A3="TT")" geht nicht.

  • Die Farben werden durch mehrstellige Nummern bspw: .Color = 49407 angegeben aber beim manuelen setzen einer bedingten Formatierung gibt man ja die Farbtöne an . Rot 220, Grün 230, Blau 241 gibt es da ne art Umrechnungsformel oder etwas in der Art?

  • Und mein letztes Problem: wenn die Bereich eingetragen wird dann wird er Komplet in $ gesetzt also so =$A$3:$M$2016 sollt aber so sein =$A3:$M2016 das wäre nicht ganz so wichtig da der Bereich jedem ausführen neu festgelegt wird.

    • Danke für die Bisherige Hilfe
    Grüße Markus
    Anzeige
    AW: Rekorder +++
    13.11.2017 15:58:18
    Markus
    OK bin selber draufgekommen was diese Frage angeht:
    Wie kann ich eine Formel eintragen die Buchstaben enthält? Die Buchstaben in einer Formel müssen ich ja in " " geschrieben werden aber in VBA kommt dann die Meldung Anweisungsende wird erwartet. bspw. .FormatConditions.Add Type:=xlExpression, Formula1:=:"=ODER($A3="RH";$A3="TT")" geht nicht.
    With Range(Cells(3, 1), Cells(x, 13))
.FormatConditions.Add Type:=xlExpression, Formula1:="=ODER($A3=""RH"";$A3=""TT"")"
.FormatConditions(.FormatConditions.Count).SetFirstPriority
With .FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 49407 'orange
.TintAndShade = 0
End With
    Einfach entschieden mehr Anführungszeichen ;)
    Anzeige
    AW: Hilfe bei Bedingter Formatierung
    13.11.2017 08:56:51
    Markus
    Hallo
    Eine kleine Grundsätzliche Frage zu den bedingten Formatierungen hätte ich noch:
    Bei einer Excel Mappe: Im Hinblick auf: Größe , Geschwindigkeit beim Öffnen, und ganz allgemein für die Performenc der Datei? Was und vielleicht warum sollte man bevorzugt verwenden bedingte Formatierungen oder Fixe. Ich hab immer so viele Formatierungen in den Dateien und die Bächen die Datei immer so schrecklich auf. Ich würde das gerne verbessern weis aber nicht so recht wie.
    Bspw. Ist es besser wenn ich bei einer Bedingten Formatierung den Rahmen soweit möglich auch gleich einstelle (Die schrieft kann man ja nicht beeinflussen) und dafür die Daten roh lasse?
    Hat da jemand einen Tip für mich bzw. erfahrungswerte in der Praxis? Ev vieleicht irgen einen guten Link der dieses Thema behandelt.

    Grüße Markus
    Anzeige
    Die Farben wollen nicht :(
    13.11.2017 17:24:35
    Markus
    Ich kapier es einfach nicht mit den Farben wie gibt man die an. bzw. wie Rechne ich das in diese mehrstellige Zahl um?
    So geht's mal Nicht: 
             .FormatConditions.Add Type:=xlExpression, Formula1:="=$A3=10"
With .FormatConditions(11).Interior
.PatternColorIndex = xlAutomatic
.Color = RGB(177, 160, 199)
.TintAndShade = 0
End With
    Kommt die Meldung Funktion nicht gefunden. Brauch ich da noch eine extra Funktion? hat die vielleicht jemand?
    Grüße Markus
    Anzeige
    AW: Farb-code
    13.11.2017 20:18:07
    Fennek
    Hallo,
    um den Farbcode zu erhalten:
    - eine Zelle von Hand färben, den Cursor auf diese Zelle stellen: im Debug-Fenster: 
    ? activecell.interior.color
    - aus RGB(102, 105, 189): color = 102 * 255^2 + 105 * 255 + 189
    - 6 vb-Constants: vbgreen, vbred, vbgray, vbyellow ...
    mfg
    Anzeige
    AW: Farb-code
    14.11.2017 08:15:37
    Markus
    Hallo, Danke für die Antwort
    Und danke für den Tipps mit: 
    Sub Farbe ()
Dim Farbe as Variant
Farbe = activecell.interior.color
End Sub

    Die Rechenformel hab ich auf meine RGB Werte angewendet aber mir kommen da andere Farbtöne raus.
    102 * 255^2 + 105 * 255 + 189= 6659514
    Bsp. RGB (177, 160, 199) ist ein Violett Ton aber es kommt eine Art Kräftiges Rosa raus.
    177*255^2 + 160 * 255 + 199= 11550424
    Hab es mit den Farben letztendlich doch hinbekommen. Wenn ich .Interior nicht oben beim With sondern unten schreib dann nimmt er es. Versteh nur nicht warum es einen Unterschied macht. 
       .FormatConditions.Add Type:=xlExpression, Formula1:="=$A3=10"
With .FormatConditions(11)
.Interior.PatternColorIndex = xlAutomatic
.Interior.Color = RGB(177, 160, 199)
.Interior.TintAndShade = 0
End With

    Mir bleibt noch eine letzte Frage wie Kombiniere ich 4 Cells Werte um in einen Rang zwei Bereiche zu bekommen. Hab mal so versucht: 
    With Range(Range(Cells(3, 1), Cells(x, 1)), Range(Cells(3, 5), Cells(x, 13)))
'War abgeleitet von dem hier::
With Range("A3:A20", Range(Cells(x, 5), Cells(x, 13))) 'Nicht anwendendbar da nicht Flexibel
    aber Excel scheint dann die zwei Bereich automatisch zu Kombinieren und trägt dann =$A$3:$M$20 ein sollt aber:
    =$A$3:$A$20 ; $E$3:$M$20 eingetragen werden.
    Grüße Markus
    Anzeige
    AW: teste "union"
    14.11.2017 10:04:07
    Fennek
    versuche anstelle von
    
With Range("A3:A20", Range(Cells(x, 5), Cells(x, 13)))
das hier
with union(Range("A3:A20"),range(Cells(x,5), cells(x,13)))
    ungetestet
    AW: Farb-code
    14.11.2017 10:38:52
    Daniel
    Hi
    wenn du einen Zellbereich, der aus mehreren nicht zusammenhängenden Blöcken besteht, ansprechen willst, dann kannst du diesen wie von Fennek gezeigt mit UNION zusammen stetzen.
    du kannst aber auch den Zellbereich direkt in der Range-Funktion als Adresse angeben.
    Dann müssen die Zellbereiche im Adressstring mit Komma getrennt angegeben werden.
    Beachte, dass es genau EIN Adressstring sein muss.
    Gibst du bei Range mehrere mit Komma getrennte Zellbereiche an (mehrer Cells oder mehrere Adresstrings), geht VBA davon aus, dass es ein Zellblock sein soll, den du über die Eckzellen (z.B. oben links / unten rechts) definierst und die einzelnen Parameter stellen die Eckzellen dar.
    Wie gesagt, wenn du mehrere Zellblöcke ansprechen willst, dann brauchst einen Adresstring, der diese Blöcke mit Komma getrennt enthält. 
    With Range("A3:A" & x & ",E3:M" & x)
    Gruß Daniel
    Anzeige
    Es hat geklappt :) -Das Endresultat
    14.11.2017 19:45:29
    Markus
    Hallo
    @Daniel: Danke für deine Erklärung, jetzt verstehe ich endlich warum jeder Versuch in die Hosen gegangen ist denn selbst wenn der rang keinen Fehler Produzierte. Wurde er dennoch falsch also als ein Bereich eingetragen. 
    With Range("A3:A" & x & ",E3:M" & x)

    ist ein toller Tipp und er Funktioniert auch bestens.
    @ Fennek die Code Zeile funktioniert ebenfalls, danke für die ganze Hilfe und für das Bsp. Erst darauf konnte ich erst überhaupt aufbauen. 
    with union(Range("A3:A20"),range(Cells(x,5), cells(x,13)))
    Das ist der derzeitige Code, er Funktioniert falls ihr noch irgendwelche Anmerkungen oder Verbesserungsvorschläge habt dann würde ich mich darüber freuen. In jedem Fall Vielen Danke nochmal an alle die Mitgewirkt haben. Ich habe hierbei echt was gelernt.
    Sub Sortieren_und_Bedi_Formatieren()
Dim x As Long
Dim Formula1 As Variant
x = LezteZ(Sheets("Tabelle1")) ' Funktion ermittelt die Letzte beschriebenen Zeile
With ThisWorkbook.Worksheets("Tabelle1").Sort
.SortFields.Clear
.SortFields.Add Key:=Range(Cells(5, 7), Cells(x, 7)), SortOn:=xlSortOnValues, Order: _
=xlAscending, DataOption:=xlSortNormal
.SortFields.Add Key:=Range(Cells(5, 5), Cells(x, 5)), SortOn:=xlSortOnValues, Order: _
=xlAscending, DataOption:=xlSortNormal
.SortFields.Add Key:=Range(Cells(5, 6), Cells(x, 6)), SortOn:=xlSortOnValues, Order: _
=xlAscending, DataOption:=xlSortNormal
'.SortFields.Add Key:=Range(Cells(5, 1), Cells(x, 1)), SortOn:=xlSortOnValues,  _
Order:=xlAscending, DataOption:=xlSortNormal
.SetRange Range(Cells(2, 1), Cells(x, 17))
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
x = x + 15  'Letzte Zeil des zu formatierenden bereiches
Sheets("Tabelle1").Cells.FormatConditions.Delete 'bereits vorhandene Formatierungen Löschen
With Range("A3:A" & x & ",E3:M" & x) ' Angabe des zu Formatierenden Bereiches
.FormatConditions.Add Type:=xlExpression, Formula1:="=ODER($A3=""HRH"";$A3=""TWT"")" _
With .FormatConditions(1)
.Interior.PatternColorIndex = xlAutomatic
.Interior.Color = RGB(255, 255, 0)
.Interior.TintAndShade = 0
.Font.Color = RGB(255, 0, 0)   'Schrift Farbe
End With
.FormatConditions.Add Type:=xlExpression, Formula1:="=ODER($A3=""2g"";$A3=""2t"";$ _
A3=2)"
With .FormatConditions(2)
.Interior.PatternColorIndex = xlAutomatic
.Interior.Color = RGB(217, 217, 217)
.Interior.TintAndShade = 0
End With
.FormatConditions.Add Type:=xlExpression, Formula1:="=UND($A3=1;$B3=40)"
With .FormatConditions(3)
.Interior.PatternColorIndex = xlAutomatic
.Interior.Color = RGB(255, 255, 255) '
.Interior.TintAndShade = 0
.Font.Color = RGB(255, 0, 0)   'Schriftfarbe
End With
.FormatConditions.Add Type:=xlExpression, Formula1:="=$A3=1"
With .FormatConditions(4)
.Interior.PatternColorIndex = xlAutomatic
.Interior.Color = RGB(255, 255, 255)
.Interior.TintAndShade = 0
End With
.FormatConditions.Add Type:=xlExpression, Formula1:="=$A3=3"
With .FormatConditions(5)
.Interior.PatternColorIndex = xlAutomatic
.Interior.Color = RGB(255, 102, 153)
.Interior.TintAndShade = 0
End With
' usw.
End With
Range("A1").Select
End Sub
Function LezteZ(wks As Worksheet) As Long
'Zählt die Anzahl der benutzten Zeilen und gibt sie aus.
'Es werden alle Zeilen berücksichtigt ebenfalls ausgeblendeten.
'Es werden die Zeilen die zbs. durch Filtern ausgeblendet wurden berücksichtigt.
Dim lngFirst As Long, lngLast As Long, lngTmp As Long
With Application
If .CountA(wks.Cells) = 0 Then Exit Function
If .CountA(wks.Rows(wks.Rows.Count)) Then
LezteZ = wks.Rows.Count: Exit Function
End If
lngLast = wks.Rows.Count
Do While lngLast > lngFirst + 1
lngTmp = (lngFirst + lngLast) \ 2
If .CountA(wks.Rows(lngTmp).Resize(lngLast - lngTmp)) Then _
lngFirst = lngTmp Else lngLast = lngTmp
Loop
If .CountA(wks.Rows(lngLast)) Then LezteZ = lngLast Else LezteZ = lngFirst
End With
End Function
    Grüße Markus
    Anzeige
    ;

    Beliebteste Forumthreads (12 Monate)

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