Zar muss ein bedingtes Format auf die Werte in Spalte A eines Arbeitsblatts anwenden, kann jedoch nicht herausfinden, wie die Regeln lauten könnten. Neben Spalte A hat er auch Daten, die in Spalte B beginnen, und er fügt regelmäßig neue Datenspalten hinzu. Wenn Daten in allen ungeraden Spalten vorhanden sind, die mit B beginnen, möchte Zar, dass ein Format in Spalte A angewendet wird. (B ist die Datenspalte 1 für sein Arbeitsblatt, daher betrachtet er sie als ungerade.) Wenn Daten in allen geraden Spalten vorhanden sind, die mit beginnen C, Zar möchte, dass in Spalte A ein anderes Format angewendet wird. Wenn in allen Datenspalten Daten vorhanden sind, die mit B beginnen – wie viele es auch sein mögen -, möchte er, dass ein drittes Format angewendet wird.

Wie Zar zweifellos herausgefunden hat, können Sie einfach eine Formel erstellen, um festzustellen, ob Informationen in den Spalten B und C vorhanden sind, und die Formatierung entsprechend anwenden. Tatsächlich reicht eine einfache Formel wie diese aus:

=COUNTA(B1:C1)=2 =COUNTA(B1)=1 =COUNTA(C1)=1

Die erste Formel gibt True zurück, wenn Informationen in B und C vorhanden sind, die zweite, wenn Informationen in B vorhanden sind, und die dritte, wenn Informationen in C vorhanden sind. Solange Sie für jede Regel / Formel „Stop If True“ auswählen, dann funktioniert Ihre Formatierung einwandfrei.

Das Erstellen einer Formel für mehrere Spalten jenseits von B und C ist nur unwesentlich schwieriger. Die gleichen drei Arten von Formeln wären in der Reihenfolge wie folgt:

=COUNTA(B1:G1)=6)

=COUNTA(B1,D1,F1)=3 =COUNTA(C1,E1,G1)=3

Sie können den Formeln bei Bedarf problemlos zusätzliche Zellreferenzen hinzufügen. Ein solcher Ansatz gibt True nur unter drei Bedingungen zurück: Wenn ALLE Zellen im Bereich B1: G1 etwas enthalten, wenn ALLE ungeraden Zellen (B1, D1, F1) etwas enthalten und wenn ALLE geraden Zellen (C1, E1, G1) haben etwas in sich. Es wird nicht True zurückgegeben, wenn nur einige der Zellen im Bereich Werte enthalten. Zum Beispiel gibt es Werte in den Zellen B1, C1 und E1, dann wird nicht True zurückgegeben und keines der Kriterien für die Formatierung wird erfüllt.

Obwohl diese alle mit der angegebenen Einschränkung gut funktionieren, sind sie nicht genau das, wonach Zar sucht – er möchte eine Formel, die erkennt, wie viele Spalten Woche für Woche verwendet werden, während er weiterhin Daten zu Spalten hinzufügt und die anpasst Formel entsprechend, ohne dass die Formel manuell bearbeitet werden muss, um die hinzugefügten Daten zu berücksichtigen. Mit anderen Worten, wenn er Daten zu Spalte H hinzufügt, möchte er, dass die Formeln automatisch angepasst werden, um die hinzugefügte Spalte zu berücksichtigen:

=COUNTA(B1:H1)=7)

=COUNTA(B1,D1,F1,H1)=4 =COUNTA(C1,E1,G1)=3

Das ist offensichtlich ein komplexeres Bedürfnis. Der beste Weg, um das Problem anzugehen, besteht möglicherweise darin, eine benutzerdefinierte Funktion (ein Makro) zu erstellen, mit der ein Zellbereich untersucht und festgestellt werden kann, ob eines der drei Kriterien erfüllt ist.

Betrachten Sie das folgende Makro:

Function CellChk(crng As Range) As String     Dim iNumOdds As Integer     Dim iNumEvens As Integer     Dim iOdds As Integer     Dim iEvens As Integer     Dim iTots As Integer     Dim iTotCells As Integer     Dim rWork As Range     Dim rCell As Range     Dim iLastCol As Integer     Dim sTemp As String

iOdds = 0     iEvens = 0     iTots = 0

' Figure out the real last column in the worksheet and set range     iLastCol = ActiveSheet.Cells.Find(What:="*", _         SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, _         LookIn:=xlFormulas).Column     Set rWork = Range(Cells(crng.Row, 2), Cells(crng.Row, iLastCol))



iTotCells = rWork.Count     iNumOdds = (iTotCells + 1) \ 2    ' Number of odd columns     iNumEvens = iTotCells - iNumOdds  ' Number of even columns

For Each rCell In rWork         If rCell <> "" Then             If ((rCell.Column - 1) Mod 2) = 1 Then                 iOdds = iOdds + 1             Else                 iEvens = iEvens + 1             End If             iTots = iTots + 1         End If     Next rCell

sTemp = ""

If iTots = iTotCells Then         sTemp = "t"

ElseIf iOdds = iNumOdds Then         sTemp = "o"

ElseIf iEvens = iNumEvens Then         sTemp = "e"

End If     CellChk = sTemp End Function

Sie verwenden das Makro, indem Sie ihm eine Adresse in der Zeile übergeben, die Sie überprüfen möchten.

Wenn Sie beispielsweise die bedingte Formatierungsregel auf Zelle A3 anwenden, übergeben Sie dem Makro die Adresse B3 oder C3 – alles außer A3, da dies einen Zirkelverweis verursacht. Das Makro sucht nach der letzten in dieser Zeile verwendeten Zelle und bestimmt dann, wie viele ungerade und gerade Zellen etwas enthalten. Das Makro gibt einen von vier Werten zurück. Wenn das erste Kriterium erfüllt ist (alle Zellen in der Zeile, die mit Spalte B beginnen, enthalten etwas), wird ein „t“ zurückgegeben. Wenn alle ungeraden Spalten (wobei B die erste ungerade Spalte ist) etwas enthalten, wird „o“ zurückgegeben. Wenn alle geraden Spalten (wobei C die erste gerade Spalte ist)

Haben Sie etwas in ihnen, dann wird „e“ zurückgegeben. Wenn keines der drei Kriterien erfüllt ist, gibt die Funktion nichts zurück.

Sie müssen noch drei bedingte Formatierungsregeln einrichten, die auf der Auswertung einer Formel beruhen. Hier sind drei, die Sie mit diesem Makro verwenden können:

=CellChk(B1)="t")

=CellChk(B1)="o")

=CellChk(B1)="e")

Diese Beispiele dienen zum Anwenden eines bedingten Formats auf Zelle A1; Passen Sie die Zellreferenzen an die richtige Zeile an, die das Makro analysieren soll. Denken Sie daran, dass das Makro berechnet, wie viele Zellen in der Zeile tatsächlich angezeigt werden sollen, obwohl Sie eine einzelne Zelle angeben (in diesen Beispielen B1).

_Hinweis: _

Wenn Sie wissen möchten, wie die auf dieser Seite (oder auf einer anderen Seite der ExcelTips-Websites) beschriebenen Makros verwendet werden, habe ich eine spezielle Seite vorbereitet, die hilfreiche Informationen enthält.

Klicken Sie hier, um diese spezielle Seite in einem neuen Browser-Tab zu öffnen.

ExcelTips ist Ihre Quelle für kostengünstige Microsoft Excel-Schulungen.

Dieser Tipp (5945) gilt für Microsoft Excel 2007, 2010, 2013, 2016, 2019 und Excel in Office 365.