Mit VBA 7 bedingte Formatierungen

Computer: Software & Programmierung Visual Basic
#1

Hallo zusammen,
ich habe bereits das Forum durchforstet, um die Frage nicht doppelt zu stellen, bin jedoch nicht fündig geworden.
Folgendes Problem, ich möchte einen Arbeitsplan für 7 Mitarbeiter erstellen. Jeder Mitarbeiter hat eine Farbe (Zellenfarbe).
Folgenden Code habe ich:

Sub Holi()
’
’ Holi Makro
’
’
Range(„B3:V34“).Select
ActiveWindow.SmallScroll Down:=-21
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="="„C“""
Selection.FormatConditions(1).Interior.ColorIndex = 34
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="="„D“""
Selection.FormatConditions(2).Interior.ColorIndex = 39
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="="„S“""
Selection.FormatConditions(3).Interior.ColorIndex = 36
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="="„St“""
Selection.FormatConditions(1).Interior.ColorIndex = 35
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="="„Sa“""
Selection.FormatConditions(1).Interior.ColorIndex = 40
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="="„N“""
Selection.FormatConditions(1).Interior.ColorIndex = 38
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="="„P“""
Selection.FormatConditions(1).Interior.ColorIndex = 15
End Sub

Kann ich den Fehler umgehen, so dass ich 7 anstelle der NUR 3 bedingten Formatierungen machen kann?
Der Code dürfte richtig sein.
Excel habe ich das 2007ner
Vielen Dank für die Hilfe schon einmal. Euer Forum ist sehr hilfreich!
Grüße
Nadine

#2

Hallo,

ich kenne mich weder mit VBA Programmierung noch mit Excel 2007 aus. Mit Excel 2010 lassen sich mehr als 3 bedingte Formatierungen ganz herkömmlich machen.
Sorry dass ich nicht helfen kann.
LG Pfefferoni

#3

Hallo Nadine,

Ich verstehe Dein Problem glaube ich noch nicht so richtig. Wenn Du doch mit VBA arbeiten kannst, kannst Du doch den Zellinhalt per VBA auslesen und die Zelle entsprechend färben. Die Frage ist dann nur, wann die Zellfärbung geschehen soll: beim öffnen/schließen der Datei, per Knopfdruck oder direkt aus einer Zellüberwachung?
Dann können wir mal weiter schauen… :wink:

Gruß
Arnold

#4

Kann es sein, dass du dich einfach bei den Aufzählungen vertippt hast? Beim Ausdruck

Selection.FormatConditions(i). usw

läuft i bei deinem Code von 1 bis 3, um dann wieder bei 1 anzufangen. Damit überschreibst du dir die 1. Bedingung wieder und wieder.

Ich habe nur Office 2010 zur Verfügung, da läuft dein Skript tadellos, sobald ich die FormatCondition von 1 bis 7 hochzähle:

Sub Holi()
’
’ Holi Makro
’
’
Range(„B3:V34“).Select
ActiveWindow.SmallScroll Down:=-21
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="="„C“""
Selection.FormatConditions(1).Interior.ColorIndex = 34
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="="„D“""
Selection.FormatConditions(2).Interior.ColorIndex = 39
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="="„S“""
Selection.FormatConditions(3).Interior.ColorIndex = 36
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="="„St“""
Selection.FormatConditions(4).Interior.ColorIndex = 35
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="="„Sa“""
Selection.FormatConditions(5).Interior.ColorIndex = 40
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="="„N“""
Selection.FormatConditions(6).Interior.ColorIndex = 38
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="="„P“""
Selection.FormatConditions(7).Interior.ColorIndex = 15
End Sub

Falls Excel 2007 allerdings nur 3 bedingte Formatierungen pro Zelle erlaubt (ich weiß, dass es bei Excel 2003 noch so war), dann wird das aber nicht funktionieren. Dann kannst du nur die Zellfarbe im Skript direkt ändern (ohne bedingte Formatierung).

Viele Grüße,
BellHouse

#5

Hllo Nadine,
welche Fehlermeldung kommt denn, und bei welcher Zeile? Excel 2007 kann nämlich mehr als 3 bedingte Formatierungen; ich habe daher die Vermutung, dass das Dokument noch im alten Format vorliegt; dann geht’s natürlich nicht.

lg
Katharina

#6

Hi.
So kompliziert (mittels Makro) brauchst du das doch gar nicht machen.
Die bedingte Formatierung ist ab 2007 mächtiger geworden.

Hier kannst du unter
Bedingte Formatierung
Regeln verwalten
Neue Regel
Formel zur Ermittlung der zu formatierenden Zellen verwenden
Bedingung für dein Feld =$c$2=„P“
Formatierung auswählen (deine Farbe)
wird angewendet auf =…
Und dies dann für alle Mitarbeiter (Mitarbeiter = Regel) erweitern.

Hoffe es hilft dir weiter.
Grüße
Jonn

#7

Grüß dich,

wie muss ich denn den Code schreiben um lediglich die Zelle auszulesen?
Ein Beispiel reicht:

Range(„B3:V34“).Select
ActiveWindow.SmallScroll Down:=-21
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="="„C“""
Selection.FormatConditions(1).Interior.ColorIndex = 34

Das ganze soll dann passieren wenn ich das Makro per Button in der Liste laufen lasse.

Danke lg
Nadine

#8

Hallo Nadine,
dein Code ist im Prinzip richtig, macht aber nichts anderes, als die bedingte Formatierung automatisch einzusetzen, anstatt manuell. Und die ist definitiv auf 3 Möglichkeiten begrenzt. Wenn du mehr Möglichkeiten haben willst, wirst du eine eigene Routine schreiben müssen und das Worksheet_Change-Ereignis einsetzen. Ein gutes Beispiel dafür findest du hier:
http://hajo-excel.de/vba_format.htm

#9

Vielen Dank, das Beispiel Tabelle 3 (in Format1) ist „meine Lösung“ - aber ist das ein Makro? Es funktioniert in der Tabelle wunderbar, aber wo sehe ich wie es so eingerichtet wurde?
Vielen Dank für deine Hilfe!

Grüße

#10

Tut mir leid, aber bei bedingter Formatierung bin ich nicht gut drauf.

#11

Hallo Nadine,

Ich habe hier mal einen kurzen Code geschrieben, den Du erweitern könntest:

Option Base 1

Sub Zellenfaerben()

Dim Zelle As Range

Dim MA(5, 2)
MA(1, 1) = "Müller"
MA(2, 1) = "Meier"
MA(3, 1) = "Schulze"
MA(4, 1) = "Günther"
MA(5, 1) = "Huber"
MA(1, 2) = 34
MA(2, 2) = 36
MA(3, 2) = 39
MA(4, 2) = 35
MA(5, 2) = 40

 For Each Zelle In Range("B3:V34")
 For i = 1 To UBound(MA)
 If Zelle = MA(i, 1) Then Zelle.Interior.ColorIndex = MA(i, 2)
 Next
 Next
End Sub

Die Namen der Mitarbeiter und der dazugehörigen Farben kannst Du beliebig erweitern. Du musst dann nur in der Zeile

 Dim MA(neueZahl,2)

eintragen.
Den Zielbereich kann man natürlich auch ändern oder flexibler gestalten.

Gruß
Arnold

#12

aber wo sehe ich wie es so eingerichtet
wurde?

Öffne Visual Basic (mit Alt + F11). Im linken Fenster siehst du die einzelnen Mappen mit den Tabellen. Mache einen Doppelklick auf die Tabelle, die dich interessiert (in deinem Fall Tabelle 3) und schon siehst du im Hauptfenster das Makro.
Du musst übrigens auch bei dir das Makro in die Tabelle schreiben, in der es benötigt wird damit es funktioniert, und nicht wie sonst üblich in ein Modul.
Sollten sich noch weitere Fragen ergeben, dann melde dich. Du musst nur immer etwas Geduld haben, weil ich nicht immer online bin.

Grüße

#13

Mit bedingter Formatierung ist Dir da nicht geholfen, die ist auf drei Bedingungen begrenzt.
Aber es gibt einen VBA-Code für das Ereigniss Change beim Sheet:

Private Sub Worksheet\_Change(ByVal Target As Range)
 Dim Bereich As Range
 Dim MyCell As Range
 Set Bereich = Range("B3:v34")
 For Each MyCell In Bereich
 Select Case MyCell.Value
 Case "A"
 MyCell.Interior.ColorIndex = 3
 Case "B"
 MyCell.Interior.ColorIndex = 4
 Case "C"
 MyCell.Interior.ColorIndex = 6
 Case "D"
 MyCell.Interior.ColorIndex = 7
 Case "E"
 MyCell.Interior.ColorIndex = 8
 Case "F"
 MyCell.Interior.ColorIndex = 17
 Case "G"
 MyCell.Interior.ColorIndex = 20


 End Select
Next

End Sub

Da die ColorIndex_Werte von 2003 zu 2007, zu 2010 etc und dann noch abhängig vom OS (Win XP, Vista, 7, 8) jeweils unterschiedlich sind, musst Du sie bei Dir selbst mal ermitteln… Dabei hilft:

Sub farbe()
For i = 1 To 32
 Cells(i, 1) = i
 Cells(i, 2).Interior.ColorIndex = i
Next i
End Sub
#14

Hallo Nadine

Ich weiß zwar nicht, welche 3 Bedienungen haben willst.
würde sagen einfach die 3 Bedienungen mit’n Makro Rekorder aufzeichnen.

Grüße
Fred

#15

Hallo missmistersland,

dafür reichen meine Kenntnisse leider nicht.
Da kann ich nicht weiter helfen.

Gruß Hugo

#16

Vielen lieben Dank.
Beste Grüße Nadine

#17

Hallo
Also das Programm ist natürlich quatsch. Es geht irgendwie nicht. was willst Du machen?
Irgendwie möchtest Du ja einer Zelle eine Hintergrundfarbe verpassen und einer Zelle ein Format hinzufügen, wie z.B. =A1 oder so. Dies ginge auch, hättest Du nicht mit Range(„B3:V34“).Select einen ganzen Block definiert.
Du must also die Formel nur in eine zelle schieben auf einmal.
Die Zeile
Selection.FormatConditions.Add Type:=xlCellValue,
Operator:=xlEqual, Formula1:="="„D“""
ist zwar gut gemeint, aber fehlt vor der Selection z.B. ein Range(„A1“).Select
Für das Einfügen vom Format kannst Du auch z.B. Ganz einfach diesen Code verwenden:
Selection.FormulaR1C1 = „=“„C“""

Oder den Code wie in der Microsoft Hilfe:
With Worksheets(1).Range(„e1:e10“).FormatConditions _
.Add(xlCellValue, xlGreater, „=$a$1“)
With .Borders
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 6
End With
With .Font
.Bold = True
.ColorIndex = 3
End With
End With

Aufbauen.

Grüsse Sebastian

#18

Hallo,
dafür benötigt man kein Makro, Excel 2007 kann mehr als 3 bedingte Formatierungen. Die Datei muss allerdings im *.xlsx oder *.xlsm Format gespeichert sein.
Gruß
Brandis