Yep, das funktioniert - habe mich wahrscheinlich falsch ausgedrückt.
Wie kann ich im "Manager für Regeln..." die Regeln so anzeigen lassen, dass er die betreffenden Ranges (es sind ja verschiedene Überschriften bzw. Strings mit der gleichen Regeln betroffen) in der betreffenden Regel anzeigt?
Zur Zeit wird es wie folgt angezeigt:
[ Bild bitte so als Datei hochladen: Klick mich! ]
[ Bild bitte so als Datei hochladen: Klick mich! ]
[ Bild bitte so als Datei hochladen: Klick mich! ]
[ Bild bitte so als Datei hochladen: Klick mich! ]
Schön wäre es, wenn es wie folgt angezeigt würde "Zellwert ..." =$M:$Q;$R:$V;$W:$W;$X:$X (Sahnehäubchen natürlich)
Anbei File - ein Freund hat mir noch etwas geholfen.
Code:
Option Explicit
Function FindeZellen(Suchbereich As Range, Suchtext As String) As Range
Dim rngB As Range
Dim rngGefundeneZellen As Range
Dim rngF As Range
Dim strErsteAdresse As String
Set rngF = Suchbereich.Find(what:=Suchtext, LookIn:=xlValues, LookAt:=xlPart, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
If Not rngF Is Nothing Then
strErsteAdresse = rngF.Address
Set rngGefundeneZellen = rngF
Do
Set rngF = Suchbereich.FindNext(rngF)
If Not rngF.Address = strErsteAdresse Then
Set rngGefundeneZellen = Application.Union(rngGefundeneZellen, rngF)
Else
Exit Do
End If
Loop
Set FindeZellen = rngGefundeneZellen
End If
End Function
Sub CondFeature(Feature As String, rngFeature As Range)
Dim rngF As Range
Set rngF = FindeZellen(rngFeature, Feature)
If Not rngF Is Nothing Then
With rngF.EntireColumn.FormatConditions.Add(Type:=xlTextString, TextOperator:=xlEqual, String:="red")
.SetFirstPriority
With .Interior
.PatternColorIndex = xlAutomatic
.Color = 11513855
.TintAndShade = 0
End With
.StopIfTrue = False
End With
With rngF.EntireColumn.FormatConditions.Add(Type:=xlTextString, TextOperator:=xlEqual, String:="yellow")
.SetFirstPriority
With .Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent4
.TintAndShade = 0.599963377788629
End With
.StopIfTrue = False
End With
' ... usw.
Sub ConditionalFormatSheetBudget()
Dim rngB As Range ', rngF As Range
With Worksheets("Budget 2017")
.Cells.FormatConditions.Delete
Set rngB = .Range(.Cells(6, 1), .Cells(6, .Columns.Count).End(xlToLeft))
End With
'====================================================================================================
Range("H7:H5000").Select '------------------------------------> Row to be updated with new parts
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=UND($H7>0;$H7<>$G7)"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 16756630
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
'====================================================================================================
End Sub
LG Yvonne
PS: Bin jetzt erst mal 2 Wochen im Urlaub und wünsche Dir eine schöne Zeit !
Public Function FindeZellen(Suchbereich As Range, ByVal Suchtext As String) As Range
Dim rngB As Range
Dim rngGefundeneZellen As Range
Dim rngF As Range
Dim strErsteAdresse As String
Set rngF = Suchbereich.Find(What:=Suchtext, _
After:=Suchbereich.Cells(Suchbereich.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)
If Not rngF Is Nothing Then
strErsteAdresse = rngF.Address
Set rngGefundeneZellen = rngF
Do
Set rngF = Suchbereich.FindNext(rngF)
If Not rngF.Address = strErsteAdresse Then
Set rngGefundeneZellen = Application.Union(rngGefundeneZellen, rngF)
Else
Exit Do
End If
Loop
Set FindeZellen = rngGefundeneZellen
End If
End Function
Sub CondFeature(rngFeature As Range, ParamArray Feature())
Dim i As Long
Dim rngF() As Range
ReDim rngF(0 To UBound(Feature) + 1)
For i = 1 To UBound(Feature) + 1
Set rngF(i) = FindeZellen(rngFeature, Feature(i - 1))
Next i
For i = 1 To UBound(rngF)
If Not rngF(0) Is Nothing Then
If Not rngF(i) Is Nothing Then
Set rngF(0) = Application.Union(rngF(0), rngF(i))
End If
Else
If Not rngF(i) Is Nothing Then
Set rngF(0) = rngF(i)
End If
End If
Next i
If Not rngF(0) Is Nothing Then
With rngF(0).EntireColumn.FormatConditions
With .Add(Type:=xlTextString, TextOperator:=xlEqual, String:="red")
.SetFirstPriority
With .Interior
.PatternColorIndex = xlAutomatic
.Color = 11513855
.TintAndShade = 0
End With
.StopIfTrue = False
End With
With .Add(Type:=xlTextString, TextOperator:=xlEqual, String:="yellow")
.SetFirstPriority
With .Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent4
.TintAndShade = 0.599963377788629
End With
.StopIfTrue = False
End With
' ...
End With
End If
End Sub
Sub ConditionalFormatSheetBudget()
Dim rngB As Range
With Worksheets("Budget 2017")
.Cells.FormatConditions.Delete
Set rngB = .Range(.Cells(6, 1), .Cells(6, .Columns.Count).End(xlToLeft))
End With
Call CondFeature(rngB, "Part Status", "Country of Origin", "Back End", "Fab", "in %")
Call CondQty(rngB, "SAVG QTY", "BW GR", "BW Order")
'====================================================================================================
'------------------------------------> Row to be updated with new parts
With Range("H7:H5000").FormatConditions.Add(Type:=xlExpression, Formula1:="=UND($H7>0;$H7<>$G7)")
.SetFirstPriority
With .Interior
.PatternColorIndex = xlAutomatic
.Color = 16756630
.TintAndShade = 0
End With
.StopIfTrue = False
End With
'====================================================================================================
End Sub
Gruß Uwe
Folgende(r) 1 Nutzer sagt Danke an Kuwer für diesen Beitrag:1 Nutzer sagt Danke an Kuwer für diesen Beitrag 28 • YvonneW
(03.09.2016, 22:57)YvonneW schrieb: Eine Einfärbung der Zellen/Schriftfarbe ohne Conditional Format wie von Ralf vorgeschlagen ist mir zu restriktiv für die betreffenden Files, kann aber mal in Zukunft für eine interessante Option sein.
das Problem sehe ich jetzt nicht, das ist doch das Gleiche:
Bei der bedingten Formatierung benötigst Du eine Formel für die Bedingung und Du definierst dann die Farbe und das Muster.
Bei der Formatierung durch ein Makro wird genauso die Bedingung benutzt und dann Farbe und Muster gesetzt.
Code:
Select Case Target.Value 'Prüfung auf den Inhalt der Zelle
Case "yellow" 'wenn yellow drin steht, mache das Folgende
With .Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent4
.TintAndShade = 0.599963377788629
End With
Case "no" 'wenn no drin steht, bringe ne MsgBox
MsgBox "no"
Case "only process"
MsgBox "only process"
Case "no process"
MsgBox "no process"
Case Else
MsgBox "nichts vorhanden!"
End Select
Das geht genauso mit einer IF-THEN -ELSE Formel, auch gestaffelt und mehrfach hintereinander.
…Aber nicht wieder (automatisch) zurück, Rabe,
falls sich mal etwas ändert! Das Makro färbt und mustert die Zelle, die Bedingt-Formatierung ein Format-Objekt, das von der Xl-Steuerung im Regel-Erfüllungsfall priorisiert wird. Das eigentliche Zellformat bleibt erhalten und kann jederzeit wieder aktiv werden. Das erfordert in einem Makro mit Zelldirektformatierung u.U. erheblichen zusätzlichen Aufwand.
Aber da kommt mir eine Idee. Du könntest ja mal untersuchen wie es wäre, wenn man eine simple benannte Konstante des Blattes anlegt (zB MusterFlipFlop =FALSCH ), die dann von einer ebenso einfachen Bedingt-Format-Regel benutzt wird, um deren Objekt zu formatieren. Das Makro muss dann nur noch im yellow-Fall diese Konstante auf WAHR setzen. In allen anderen Fällen wird sie (wieder) auf FALSCH gesetzt und die alte Formatierung erscheint. Das ist dann zwar immer noch nicht automatisch, aber immerhin wesentlich weniger aufwendig zu programmieren.
Gruß, Castor
15.09.2016, 05:45 (Dieser Beitrag wurde zuletzt bearbeitet: 15.09.2016, 05:45 von Rabe.)
Hi,
(15.09.2016, 01:35)Castor schrieb: …Aber nicht wieder (automatisch) zurück, Rabe,
falls sich mal etwas ändert!
[...]
(zB MusterFlipFlop =FALSCH ), die dann von einer ebenso einfachen Bedingt-Format-Regel benutzt wird, um deren Objekt zu formatieren. Das Makro muss dann nur noch im yellow-Fall diese Konstante auf WAHR setzen. In allen anderen Fällen wird sie (wieder) auf FALSCH gesetzt und die alte Formatierung erscheint. Das ist dann zwar immer noch nicht automatisch, aber immerhin wesentlich weniger aufwendig zu programmieren.
stimmt, da hast Du recht. Das Zurücksetzen auf den vorigen manuell formatierten Zustand erfolgt so nicht automatisch(edit: , aber mit der bedingten Formatierung geht das doch auch nicht, das muß doch dort auch über eine weitere bF gelöst werden).
Ja, klar, mit Variablen/benannten Konstanten geht es auch. Aber dann brauche ich Makro und bF, dann kann ich auch gleich das Makro weglassen.
Aber vielleicht verstehe ich die ganze Aufgabe ja auch gar nicht.
In meinem Beispiel gibt es 5 Fälle: in den Zellen steht entweder "no", "no process", "only process", "yellow" oder nichts.
Wenn einer der Begriffe drin steht, wird die Zelle über die bF gefärbt (oder über das Case-Makro), wenn nichts drin steht, wird die Farbe wieder entfernt.
Der einzige Unterschied:
Im Falle der bF ist die Zelle dann so wie vorher gefärbt (farbig, wenn sie vorher manuell gefärbt war, ohne farbe, wenn sie vorher ohne Farbe war); im Falle des Makros ist die Zelle nicht mehr gefärbt.
Ok, wenn das der springende Punkt ist, dann geht es nur über bF.
@ Uwe
Super, genau so wollte ich es haben !
@ Castor, Ralf
- Es werden viele verschiedene Eingaben der User gemacht, nicht nur beschränkt auf die Begriffe welche per Conditonal Format einzufärben sind.
- Es werden immer wieder Änderungen vorgenommen und die Zellfarbe sollte sich dann sofort ändern.
- Oft werden Zeilen eingefügt oder Inhalte aus anderen Files einkopiert, was die Anzeige bei Conditional Format aufbläht.
- Keiner ist so diszipliniert hier ein Makro drüberlaufen zu lassen -> deswegen Makro mit "Conditional Format" um diies alles zu korrigieren.
Vielen Dank an alle & herzliche Grüße aus Nürnberg.
P.S.
Ralf, netter Spruch mit der deutschen
:75: :D
(09.10.2016, 02:12)YvonneW schrieb: - Oft werden Zeilen eingefügt oder Inhalte aus anderen Files einkopiert, was die Anzeige bei Conditional Format aufbläht.
- Keiner ist so diszipliniert hier ein Makro drüberlaufen zu lassen -> deswegen Makro mit "Conditional Format" um diies alles zu korrigieren.
da das so lange her ist, habe ich jetzt das Ganze nicht noch mal gelesen, also falls ich falsch gedacht habe, den Beitrag nicht beachten.
Mit
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
[...dein Code...]
End Sub
kannst Du bei Änderungen in dem Arbeitsblatt automatisch das Korrigieren durchführen lassen, da wird keine Disziplin der Benutzer mehr benötigt. Und der Bereich, den es betrifft kann auch automatisiert werden, falls sich die Zeilenzahl ändert oder so.
da das so lange her ist, habe ich jetzt das Ganze nicht noch mal gelesen, also falls ich falsch gedacht habe, den Beitrag nicht beachten.
Mit
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
[...dein Code...]
End Sub
kannst Du bei Änderungen in dem Arbeitsblatt automatisch das Korrigieren durchführen lassen, da wird keine Disziplin der Benutzer mehr benötigt. Und der Bereich, den es betrifft kann auch automatisiert werden, falls sich die Zeilenzahl ändert oder so.
![[-]](https://www.clever-excel-forum.de/images/collapse.png)
