Conditional Formatting in Abhängigkeit der Spaltenüberschrift

[YvonneW]

Hallo Yvonne,


so wie ich es hier im Beitrag #6 schon schrieb.

Gruß Uwe

Hallo Uwe

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
 
  Call CondFeature("Part Status", rngB)
  Call CondFeature("Country of Origin", rngB)
  Call CondFeature("Back End", rngB)
  Call CondFeature("Fab", rngB)
  Call CondPercent("in %", rngB)
  Call CondQty("SAVG QTY", rngB)
  Call CondQty("BW GR", rngB)
  Call CondQty("BW Order", rngB)
 
'====================================================================================================
  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 !

[Kuwer]

Hallo Yvonne,

so sollte es klappen:

Option Explicit

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

[Rabe]

Hi Yvonne,

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.

[Castor]

…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

[Rabe]

Hi,

…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.

[YvonneW]

Hallo liebe Excel-Spezialisten

@ 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

[Rabe]

Hi Yvonne,

- 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.

[YvonneW]

Hi Yvonne,


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.

Hi Ralf

Danke für die Info, werde ich mal probieren.