Live-Forum - Die aktuellen Beiträge
Datum
Titel
18.04.2024 21:09:11
4 Spalten aus smarter Tabelle in andere smarte Tab. kopieren
18.04.2024 20:45:35
Worksheet_Activate für alle Sheets
18.04.2024 19:30:20
VBA - Tabelle mit Mannschaftsergebnissen sortieren
18.04.2024 18:04:29
Formel fehler Wert#
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1052to1056
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1052to1056
1052to1056
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

zeilen farblich formatieren

zeilen farblich formatieren
23.02.2009 15:18:23
Frank
Hallo zusammen,
kurze Frage an alle Excelfanatiker.
Wie stelle ich es an, meine Tabelle farblich abhängig vom Zellenwert zu formatieren.
Tabelle ist ellenlang und nach Spalte A sortiert. Die Werte in A kommen vielfach doppelt, dreifach...zwanzigfach vor. Meine Idee ist es nun wegen der Übersichtlichkeit die zu formatieren.
Stelle mir das als laie etwa so vor.
Fange bei Zeile1 an - Hintergrund grau - gehe in Zeile2 schaue nach Zeichensatz (bei den Zellwerten handelt es sich nicht um Zahlen) wenn gleich wie in Zeile 1 Spalte A dann auch grau. Dies soll sich dynamisch bis zur letzten Zeile fortsetzen. Gibt es einen Unterschied zu vorherigen Zelle dann mach Zeile gelb. Das dann weiter bis zum nächsten Unterschied - dann wieder grau. Es sollen sich die Zeilen nicht abwechelnd farblich unterscheiden nur erst dort wo ein Unterschied in den Zellwerten auftaucht.
Bin schon hoch und runter gegoogelt -
Hat jemand ne Idee.
Danke
Gruß Frank

15
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: zeilen farblich formatieren
23.02.2009 15:25:08
Hajo_Zi
Hallo Frank,
Doppelte Einträge
Annahme: Deine Werte stehen in A1 bis Axx
A1 anwählen, Format, Bedingte Formatierung (bei bedingter Formatierung soll es eine Grenze von 2050 Zeilen geben, mal selber Testen)
"Formel ist" auswählen NICHT "Zellwert ist"
Bei der Formel eingeben =ZÄHLENWENN(A$1:A1;A1)>1
Bei Format dann z.B. Musterfarbe rot auswählen mit OK beenden.
Die Formatierung auf Die Zellen A2 bis Axx kopieren. Fertig
Dann werden Doppelt farblich gekennzeichnet

AW: zeilen farblich formatieren
23.02.2009 15:26:33
Daniel
Hi
Farbe ist dafür ungeeignet, das wird zu komplex, weil du der Formatierung auch noch mitgeben musst, welche Farbe du haben willst.
Einfacher gehts, wenn du einfach eine Rahmenlinie als Trennung einzeichnest, wenn sich 2 Werte unterscheiden.
Vorgehen ist einfach:
- ganze Tabelle markieren
- FORMAT - BEDINGTE FORMATIERUNG klicken
- dort "Formel" auswählen
- als Formeltext =$a1$a2 eingeben
- im Untermenü FORMAT - RAHMEN dann den "Rahmen unten" anklicken
das ist zwar nicht so plakativ wie farben, aber wesentlich einfacher zu realisieren
Gruß, Daniel
Anzeige
AW: zeilen farblich formatieren
23.02.2009 16:11:10
Frank
Hallo Hajo und Daniel,
danke für eure schnelle Hilfe. Habe soeben beides ausprobiert.
Mit der Zellfarbe ist bei zeile 607 schluss - von der Optik gewöhnungsbedürftig.
Daniels Idee dies über die bedingte Formatierung mittels Rahmen zu lösen - sieht übersichtlich aus.
Werde mich für eine idee entscheiden - ausser es weiß noch jemand wie ich es hinbekomme meine Tabelle weiß - grau im Wechsel zu gestalten.
man hängt so an seinen festgefahrenen Vorstellungen. :-)
Danke Euch beiden
Gruß aus Dresden
Frank
AW: zeilen farblich formatieren
23.02.2009 16:25:25
Daniel
Hi
vielleicht so:
1. Hilfsspalte (im Beispiel Spalte X) einfügen mit folgenden Formeln:
erste Zelle: (Formel geschrieben für X1) WAHR
weiter Zellen (formel geschrieben für X2): =wenn(A1=A2;X1;Nicht(X1)
2. Bedingte Formatierung für Tabelle anlegen, mit
Bedingung 1: =$X1 , Farbe gelb
Bedingung 2: =Nicht($X1) , Farbe Grau
wie man es ohne Hilfsspalte hinbekommt, wüsste ich aber auch nicht.
Gruß, Daniel
Anzeige
AW: zeilen farblich formatieren
23.02.2009 16:13:01
Uwe
Hi Frank,
wenn Du Dir eine Hilfsspalte leisten kannst, dann hätte ich diese Lösung für Dich:
 AB
1a1WAHR
2a1WAHR
3a1WAHR
4a2FALSCH
5a2FALSCH
6a3WAHR
7a4FALSCH
8a5WAHR
9a5WAHR
10bFALSCH

Formeln der Tabelle
ZelleFormel
B1=WAHR
B2=WENN(A2=A1;B1;NICHT(B1))
B3=WENN(A3=A2;B2;NICHT(B2))
B4=WENN(A4=A3;B3;NICHT(B3))
B5=WENN(A5=A4;B4;NICHT(B4))
B6=WENN(A6=A5;B5;NICHT(B5))
B7=WENN(A7=A6;B6;NICHT(B6))
B8=WENN(A8=A7;B7;NICHT(B7))
B9=WENN(A9=A8;B8;NICHT(B8))
B10=WENN(A10=A9;B9;NICHT(B9))

Bedingte Formatierungen der Tabelle
ZelleNr.: / BedingungFormat
A11. / Formel ist =$B1=FALSCHAbc
A21. / Formel ist =$B2=FALSCHAbc
A31. / Formel ist =$B3=FALSCHAbc
A41. / Formel ist =$B4=FALSCHAbc
A51. / Formel ist =$B5=FALSCHAbc
A61. / Formel ist =$B6=FALSCHAbc
A71. / Formel ist =$B7=FALSCHAbc
A81. / Formel ist =$B8=FALSCHAbc
A91. / Formel ist =$B9=FALSCHAbc
A101. / Formel ist =$B10=FALSCHAbc


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Spalte B kannst Du dann natürlich ausblenden.
Gruß
Uwe
(:o)
Anzeige
AW: zeilen farblich formatieren
23.02.2009 16:29:10
Frank
hallo daniel und uwe,
Hilfsspalte ist kein Thema - kann die ja nach getaner arbeit löschen.
melde mich wenn ichs geschafft habe - muss erstmal ausser haus
Gruß Frank
AW: Hilfsspalte NICHT LÖSCHEN!!!
23.02.2009 16:44:07
Uwe
Hi Frank,
NEIN, die Hilfsspalte kannst Du nicht löschen, die bedingt Formatierung bleibt dann NICHT erhalten. Wenn Du das Ganze nicht dynamisch brauchst, sondern "nach getaner Arbeit" quasi einfrierst, könnte man die Zellen per VBA dauerhaft färben (ginge auch dynamisch, könnte aber nerven wenn bei jeder Ändrung die Zellen neu formatiert werden), nicht über bedingte Formatierung.
Gruß
Uwe
(:o)
AW: Hilfsspalte NICHT LÖSCHEN!!!
23.02.2009 20:43:00
Frank
Hallo zusammen,
habe beide Versionen nun ausprobiert. Klappen wunderbar.
Da ich das irgendwie alles beim öffnen der Arbeitsmappe ablaufen lassen möchte habe ich mal den macrorecorder laufen lassen.
Funktioniert, sieht aber irgendwie komisch aus.
In Zelle W1 habe ich =wahr eingetragen
In Zelle W2 =wenn(E1=E2;W1;Nicht(W1)) zuvergleichende Zellwerte stehen jetzt in Spalte E.
Zellen bis W1000 kopiert
Hilfsspalte w nicht gelöscht sondern versteckt und die bedingten Formate zugewiesen.
Wie schreibe ich das denn richtig in VBA auch dass die formel in W2 dynamisch bis zur letzten gefundenen Zeile kopiert wird und nicht wie bei mir auf Verdacht bis 1100.
Frank
ActiveCell.FormulaR1C1 = "=TRUE"
Range("W2").Select
ActiveCell.FormulaR1C1 = "=IF(R[-1]C[-18]=RC[-18],R[-1]C,NOT(R[-1]C))"
Range("W2").Select
Selection.AutoFill Destination:=Range("W2:W1100"), Type:=xlFillDefault
Range("W2:W1000").Select
Columns("W:W").Select
Selection.EntireColumn.Hidden = True
Cells.Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$W1"
Selection.FormatConditions(1).Interior.ColorIndex = 35
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=NICHT($W1)"
Selection.FormatConditions(2).Interior.ColorIndex = 34
Range("A1").Select
Anzeige
AW: Hilfsspalte NICHT LÖSCHEN!!!
23.02.2009 23:20:09
Daniel
Hi
hier ist dein Code mal überarbeitet, dh die Formeln werden nur soweit eingegragen, wie in Spalte E Daten vorhanden sind. Ebenso habe ich überflüssige Codebestandteile entfernt.
Sub test() Dim Zeile As Long Zeile = Range("E65536").End(xlUp).Row Range("W1").FormulaR1C1 = "=TRUE" Range("W2:W" & Zeile).FormulaR1C1 = "=IF(R[-1]C[-18]=RC[-18],R[-1]C,NOT(R[-1]C))" Range("W:W").EntireColumn.Hidden = True With Cells .FormatConditions.Delete .FormatConditions.Add Type:=xlExpression, Formula1:="=$W1" .FormatConditions(1).Interior.ColorIndex = 35 .FormatConditions.Add Type:=xlExpression, Formula1:="=NICHT($W1)" .FormatConditions(2).Interior.ColorIndex = 34 End With Range("A1").Select End Sub


Gruß, Daniel

Anzeige
AW: Hilfsspalte NICHT LÖSCHEN!!!
24.02.2009 11:11:34
Frank
Hallo Daniel,
irgendwie haut er jetzt die farben durcheinander. Habe nochmal die Formataufzeichnung der Formatierung vom Macrorecorder genommen.
Mir ist dafür was ganz schlimmes aufgefallen.
Habe mich auf das sortieren und formatieren konzentriert und dabei was übersehen.
Die ganze Geschichte muss ab Zeile 2 losgehen. In Zeile 1 steht natürlich in jeder zelle ein Zeichensatz der als Überschrift dient. Zum anderen soll Zeile 1 fixiert bleiben damit eben der Überblick nicht verloren geht. Das kann man ja noch später manuell machen. Viel wichtiger ist halt, das sich die zeile 1 nicht verschiebt! Jetzt habe ich mir gedacht, nichts einfacher tauscht du eben die ganzen bezüge wo ne 1 drinsteht gegen ne 2 aus. Der sortiert dann strikt nach Alphabet und färbt die Zeilen im Wechsel ein.
Wie kann man dass anstellen? Ohne die Zeile 1 nützt mir der Rest nichts ausser dass ich wieder einiges dazu gelernt habe. Es gibt halt immer was positives!! :-)
Danke Frank

Sub sortieren()
Application.ScreenUpdating = False
Application.AskToUpdateLinks = False
Sheets("daten").Activate
Range("E1").Sort key1:=Range("E1"), order1:=xlAscending, header:=xlNo
Range("x1").Formula = "=countif(E:E,E1)"
Range("x1:x" & Range("E1").CurrentRegion.Rows.Count).FillDown
Range("E1").Sort key1:=Range("x1"), order1:=xlDescending, header:=xlNo
Selection.Insert Shift:=xlDown
Call format
Application.ScreenUpdating = True
Application.AskToUpdateLinks = True
End Sub




Sub format()
Dim Zeile As Long
Zeile = Range("E65536").End(xlUp).Row
Range("W1").FormulaR1C1 = "=TRUE"
Range("W2:W" & Zeile).FormulaR1C1 = "=IF(R[-1]C[-18]=RC[-18],R[-1]C,NOT(R[-1]C))"
Range("W:W").EntireColumn.Hidden = True
Cells.Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$W1"
Selection.FormatConditions(1).Interior.ColorIndex = 35
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=NICHT($W1)"
Selection.FormatConditions(2).Interior.ColorIndex = 34
Range("A1").Select
End Sub


Anzeige
AW: Hilfsspalte NICHT LÖSCHEN!!!
24.02.2009 13:32:10
Daniel
Hi
1. Wenn du sortierts, und die 1. Zeile die Überschrft ist, die nicht mitsortiert werden soll, dann musst den Parameter HEADER:=XLYES setzen, sonst wird die Übeschrift mitsortiert.
2. dein Sortierparameter steht in Spalte X. sind wirklich alle Spalten zwischen Spalte E und Spalte X mit einem Wert befüllt? sollten sich zum Zeitpunkt des Sortierens noch Leerspalten zwischen E und X befindenm, gehört Spalte X NICHT zum Sortierbereich mit dazu, wenn du beim Sortierbereich nur eine Zelle angibts, denn dann erweitert Excel automatisch den Sortierbereich, aber eben nur bis zur nächsten LeerZeile bzw Leerspalte.
alternativ kannst du den Zellbereich, der sortiert werden soll, vollständig angeben:

Sheets("daten").Usedrange.Sort Key:=Sheets("daten").Range("E1")....


zum 2. Makro:
hier die Anpassung an die Überschriftenzeile:
die Zellebezüge in der Bedingten Formatierung habe ich in der Z1S1-Schreibweise angegeben, das ist zuverlässiger und besser als die normale A1-Schreibweise für Zellbezüge.


Sub test()
Dim Zeile As Long
Zeile = Range("E65536").End(xlUp).Row
Range("W1").Value = "Überschrift darfst du dir selber ausdenken"
Range("W2").FormulaR1C1 = "=TRUE"
Range("W3:W" & Zeile).FormulaR1C1 = "=IF(R[-1]C[-18]=RC[-18],R[-1]C,NOT(R[-1]C))"
Range("W:W").EntireColumn.Hidden = True
Activesheet.Cells.FormatConditions.Delete
With ActiveSheet.UsedRange
.FormatConditions.Add Type:=xlExpression, Formula1:="=ZS23"
.FormatConditions(1).Interior.ColorIndex = 35
.FormatConditions.Add Type:=xlExpression, Formula1:="nicht(ZS23)"
.FormatConditions(2).Interior.ColorIndex = 34
End With
Range("A1").Select
End Sub


gruß, Daniel

Anzeige
AW: Hilfsspalte NICHT LÖSCHEN!!!
24.02.2009 15:07:08
Frank
Hallo Daniel,
Dank Deiner Hilfe habe ich mich über die Ziellinie gekämpft.
Besten Dank
Gruß Frank
AW: Hilfsspalte NICHT LÖSCHEN!!!
24.02.2009 15:36:25
Frank
...letzte Frage? :-)
Momentan sind alle zellen zwischen E und X gefüllt. Ist sogar mir aufgefallen dass wenn eine Leerzelle dazwischen sich befindet, dass dann der Sortiervorgang nicht das gewünschte ergebniss bringt.
Nun hast du geschrieben dass man mit folgendem Befehl diese Schwachstelle umgehen kann.
'Sheets("daten").UsedRange.Sort Key:=Sheets("daten").Range("E1")
Probiere gerade diesen einzufügen.
nehme diesen raus
Range("E1").Sort key1:=Range("E1"), order1:=xlAscending, header:=xlYes
und ersetzte dies mit
'Sheets("daten").UsedRange.Sort Key:=Sheets("daten").Range("E1"),order1:=xlAscending, header:=xlYes
kommt Fehler - wahrscheinlich wieder zu einfach oder zu kompliziert gedacht
kannst du mir sagen - warum?
Anzeige
AW: Hilfsspalte NICHT LÖSCHEN!!!
24.02.2009 15:39:37
Daniel
Hi
vielleicht, weil es heissen muss KEY1:= und nicht nur KEY:= ?
Gruß, Daniel
AW: Hilfsspalte NICHT LÖSCHEN!!!
24.02.2009 16:27:42
Frank
... die 1 vergessen!!! Jetzt passts!
danke nochmals
Gruß Frank

Beliebteste Forumthreads (12 Monate)

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

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige