Excel: Bedingte Formatierung automatisch auf eine neue Zeile erweitern, die über den Daten hinzugefügt wird

Question

Ich habe eine Reihe von Werten in mehreren Zeilen und Spalten. Sie sind so strukturiert, dass jede Zeile die Daten für einen bestimmten Monat anzeigt und die aktuellsten Werte ganz oben stehen. Wenn ich sie daher mit neuen Daten aktualisiere, füge ich über den vorhandenen Daten eine neue Zeile ein.

Wie kann ich sicherstellen, dass die Regeln zur bedingten Formatierung, die ich auf den alten Bereich angewendet habe, auch auf den neuen Bereich (mit einer weiteren Zeile) angewendet werden? Ich habe versucht, Tabellen oder benannte Bereiche zu verwenden, aber da ich die Zeile über den alten Daten hinzufüge, erweitert Excel den benannten Bereich/die Tabelle nicht automatisch.

edit: Das sind die vorhandenen Daten

Und ich möchte noch den Januar (rot markiert) dazu nehmen. Wenn jede Unterkategorie eine leere Zeile darüber hätte (z.B. Zeile 9), könnte ich das umgehen, aber das ist leider nicht der Fall (siehe Zeile 5).

Answer 1

Wenn ich mir Ihre Datenstruktur vor Augen halte, denke ich, dass Sie das gewünschte Ergebnis nur mit VBA erreichen können. Ich habe etwas Ähnliches wie Ihre Datenstruktur erstellt und ein Skript geschrieben, das jedes Mal ausgeführt wird, wenn Zeilen hinzugefügt werden.

1. Speichern Sie die Datei unterExcel-Arbeitsmappe mit Makros (.xlsm)*
2. Öffnen Sie den VBA-Editor mit Ctrl+F11
3. Doppelklicken Sie im VBA-Editor im linken Bereich auf Ihr Blatt.
4. Fügen Sie diesen Code ein:

Private Sub Worksheet_Change (ByVal Ziel als Bereich)
  Dim iTargRowsCnt As Long
  Dim oFCond As FormatCondition
  Dim sFCondRanges() als String
  Dim i As Long
  Dim blnFCondRangeModified als Boolean
  Dim sNewAppliesTo As String

  Wenn Ziel.Adresse = Ziel.EntireRow.Adresse, dann
    iTargRowsCnt = Ziel.Zeilen.Anzahl
    Für jede oFCond in Me.UsedRange.FormatConditions
      sFCondRanges() = Split(oFCond.AppliesTo.Address, ",")
      Für i = LBound(sFCondRanges) bis UBound(sFCondRanges)
        Wenn Not Intersect(Me.Range(sFCondRanges(i))(1), Target(1).EntireRow.Offset(iTargRowsCnt)) nichts ergibt, dann
          sFCondRanges(i) = Bereich(Bereich(sFCondRanges(i)), Bereich(sFCondRanges(i)).Offset(-iTargRowsCnt)).Adresse
          blnFCondRangeModified = Wahr
        Ende, wenn
      Nächstes 'i
      Wenn blnFCondRangeModified Dann
        Für i = LBound(sFCondRanges) bis UBound(sFCondRanges)
          sNewAppliesTo = sNewAppliesTo & sFCondRanges(i) & ","
        Nächste
        sNewAppliesTo = Links(sNewAppliesTo, Länge(sNewAppliesTo) - 1)
        oFCond.ModifyAppliesToRange Bereich(sNewAppliesTo)
        blnFCondRangeModified = Falsch
        sNewAppliesTo = ""
      Ende, wenn
    Weiter 'oFCond
  Ende, wenn

End Sub

Dieser Code wird automatisch ausgeführt, wenn Sie Zeilen einfügen. Aber seien Sie vorsichtig, wenn Sie VBA verwenden. Speichern Sie öfter, es kann etwas Unerwartetes passieren, obwohl ich versucht habe, Risiken zu minimieren. Beispielsweise ist es schwierig, einen Code zu entwerfen, der gut mit der Rückgängig-Funktion funktioniert. In diesem Fall würden bedingte Formate verzerrt, wenn Sie Zeilen einfügen und dann Rückgängig machen.

Answer 2

Ich würde einfach bei Bereichen bleiben. Fügen Sie beim Anwenden der bedingten Formatierung oben eine zusätzliche Zeile ein, verbergen Sie sie dann oder machen Sie visuell damit, was Sie wollen (ich persönlich würde ihren Zweck als Pufferzeile irgendwo vermerken, damit sie nicht versehentlich entfernt wird).

Eine andere Methode wäre, die oberste Zeile zu kopieren und sie dann als Formatierung über Ihre neue Zeile einzufügen (bin im Moment nicht an einem Computer, muss überprüfen, ob dies für die bedingte Formatierung funktioniert oder nicht) . BEARBEITEN: Funktioniert bestätigt für die bedingte Formatierung in O365 Business Plus: Excel 2016.

Answer 3

verwenden Sie den Bereich 1:1000, er kann sich in den vollen Bereich ändern (A1:AZ1000 oder so ähnlich), er wird jedoch aktualisiert, wenn Sie weitere Zeilen oder Spalten hinzufügen, sodass er beim vollen Bereich bleibt

Answer 4

Nachdem ich mit einer ähnlichen Herausforderung gekämpft hatte, fand ich heraus, dass sich durch einfaches Verweisen auf den Tabellennamen im Eintrag „Anwenden auf:“ jede Änderung in der Tabelle, einschließlich des Hinzufügens von Zeilen, automatisch anpasst :).