bedingte formartierung

hans jupp · 03.05.2004

hallo,

heute wieder mal eine frage zu excel 2002.

über die bedingte formartierung kann man von haus aus 3 regeln festlegen. ich bräuchte aber mehrere.

es geht darum, in einer tabelle, abhängig vom inhalt den zellenhintergrund farbig zu gestalten.

beispielsweise:
1=gelb
2=rot
3=blau
4=grün
5= ...

vielen dank schon mal für eure hilfe ;D

Matjes · 03.05.2004

hi kath.hundefriseursalon,

für mehr als 3 bedingte Formatierungen benötigst Du einen Makro. Der kann folgendermassen aussehen:

Code:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
  Const c_spalte = 3->entspricht C
  Dim z As Range
  For Each z In Target
    With z
      If .Column = c_spalte Then
        If 1 = .Value Then
          .Interior.ColorIndex = 36->hellgelb 6->gelb
        ElseIf 2 = .Value Then
          .Interior.ColorIndex = 38->hellrot  3->rot
        ElseIf 3 = .Value Then
          .Interior.ColorIndex = 37->hellblau 5->blau
        ElseIf 4 = .Value Then
          .Interior.ColorIndex = 35->hellgrün 4->grün
        Else ->sonst
          .Interior.ColorIndex = xlColorIndexAutomatic
        End If
      End If
    End With
  Next
End Sub

a) mit Alt+F11 den VB-Editor öffnen
b) im Projektfenster mit Doppelklick auf die Tabelle das Codefenster der Tabelle öffnen
c) das Makro per copy&paste in das Codefenster kopieren
d) die konstante c_spalte auf die gewünschte Zeilennummer setzen
e) mit Strg+S speichern
f) mit Alt+Q VB-Editor schliessen

Gruß Matjes

hans jupp · 04.05.2004

hallo matjes,

vielen dank erst mal für die ausführliche hilfe.

leider fehlt mir ein bischen der durchblick bei makros.

könntest du mir daher bitte punkt c noch mal genauer erklären?
bei mir stehen die werte in spalte j4 bis j34.

Matjes · 04.05.2004

hi kath.hundefriseursalon,

hab den Makro so angepasst, daß Spalte J, Zeile 4-34 überwacht wird.
Ist der Zellwert der geänderten Zelle
1 -> Zellhintergrund hellgelb setzen
2 -> Zellhintergrund hellrot setzen
3 -> Zellhintergrund hellblau setzen
4 -> Zellhintergrund hellgruen setzen
sonst -> Zellhintergrund farblos setzen

Code:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
 ->die Konstanten schränken den überwachten Bereich ein:
 ->Spalte J, Zeile 4-34 - kann entsprechend abgeändert werden
  Const c_Spalte = 10->entspricht Spalte J
  Const c_Zeile_anf = 4 
  Const c_Zeile_end = 34
  Dim z As Range
  For Each z In Target
    With z
      If .Column = c_Spalte Then
        If (c_Zeile_anf &lt;= .Row) And (.Row &lt;= c_Zeile_end) Then
          
         ->Zelle liegt im überwachten Bereich
         ->auf Wert prüfen und Zellhintergrund entspr. setzen
          If 1 = .Value Then
            .Interior.ColorIndex = 36->hellgelb 6->gelb
          ElseIf 2 = .Value Then
            .Interior.ColorIndex = 38->hellrot  3->rot
          ElseIf 3 = .Value Then
            .Interior.ColorIndex = 37->hellblau 5->blau
          ElseIf 4 = .Value Then
            .Interior.ColorIndex = 35->hellgrün 4->grün
          Else ->sonst
            .Interior.ColorIndex = xlColorIndexNone
          End If

        End If
      End If
    End With
  Next
End Sub

Um den Makro auf deiner Tabelle wirksam werden zu lassen, muß du ihn in das Code-Fenster der Tabelle schreiben.
Das geht folgendermassen:

a) zunächst die Mappe mit der entsprechenden Tabelle in Excel öffnen

b) mit Alt+F11 den VB-Editor öffnen

c) im Projektfenster mit Doppelklick auf die Tabelle das Codefenster der Tabelle öffnen

d) das Makro per copy&paste in das Codefenster kopieren.
Dazu alles innerhalb des grauen Rahmens (hier im Thread) selektieren -> rechte Maustaste -> Kopieren
Den Cursor in das Code-Fenster der Tabellen (siehe c))setzen -> rechte Maustaste -> Einfügen

e) ggf. die Konstanten c_Spalte, c_Zeile_anf, c_Zeile_end anpassen

f) mit Strg+S speichern

g) mit Alt+Q VB-Editor schliessen

h) auf dem Tabellenblatt die Funktion des Makros prüfen

Gruß Matjes

hans jupp · 05.05.2004

hi matjes,

vielen dank jetzt habe ich es verstanden und es funktioniert

ich habe noch ein paar kleine änderungen vorgenommen. dabei ist mir aufgefallen, daß jedesmal, wenn man einen . schreibt, sich ein pulldown-fenster öffnet - sind das die vb-befehle?

da das ganze so gut funktioniert, kann man das makro auch so abändern, daß sich abhängig vom wert der spalte j die komplette zeile von a bis l ändert - oder ist das sehr aufwendig?

Matjes · 05.05.2004

hi kath.hundefriseursalon,

Zu deiner Frage mit dem Punkt:
In dem Makro vorher ist ein Zeilenpaar->With z' und->End With' enthalten. z ist ein Objekt, nämlich die einzelne geänderte Zelle. Es folgen diverse Zeilen, die sich auf z beziehen. Um nicht jedesmal z schreiben zu müssen, gibt es die->With'-Anweisung. Man setzt ein Objekt (hier z), das bis zur->End With'-Anweisung gültig ist. In allen Zeilen dazwischen braucht man dann nur noch .Eigenschaft statt z.Eigenschft zu schreiben.
Man kann immer nur ein Objekt in dieser Schreibweise abkürzen. Ineinandergeschachtelte->With/End With'-Anweisungen sind verboten.

Wenn Du also innerhalb der->With/End With'-Anweisung einen Punkt geschrieben hast, ist das gleichbedeutend mit->z.', woraufhin der VB-Editor eine Liste der Objekte/Eigenschaften für dieses Objekt zur Auswahl stellt.

Im Makro unten habe ich die->With'-Anweisung für z aufgelöst und stattdessen eine für den zu markierenden Bereich geschrieben.

In diesem Beispiel wird der Zellhintergrund in den Zellen A-L der geänderten Zeile gesetzt.

Gruß Matjes

Code:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
 ->die Konstanten schränken den überwachten Bereich ein:
 ->Spalte J, Zeile 4-34 - kann entsprechend abgeändert werden
  Const c_Spalte = 10->entspricht Spalte J
  Const c_Zeile_anf = 4
  Const c_Zeile_end = 34
 ->Spalte, die markiert werdensollen
  Const c_Sp_von = 1->entspricht Spalte A
  Const c_Sp_bis = 12->entspricht Spalte L
  Dim z As Range
  For Each z In Target
    If z.Column = c_Spalte Then
      If (c_Zeile_anf &lt;= z.Row) And (z.Row &lt;= c_Zeile_end) Then
       ->Zelle liegt im überwachten Bereich
       ->auf Wert prüfen und Zellhintergrund entspr. setzen
        With ActiveSheet.Range(Cells(z.Row, c_Sp_von), Cells(z.Row, c_Sp_bis))
          If 1 = z.Value Then->hellgelb
            .Interior.ColorIndex = 36
          ElseIf 2 = z.Value Then->hellrot
            .Interior.ColorIndex = 38
          ElseIf 3 = z.Value Then->hellblau
            .Interior.ColorIndex = 37
          ElseIf 4 = z.Value Then->hellgrün
            .Interior.ColorIndex = 35
          Else ->sonst
            .Interior.ColorIndex = xlColorIndexNone
          End If
        End With
      End If
    End If
  Next
End Sub

hans jupp · 06.05.2004

hi matjes,

vielen dank nochmal für deine hilfe

du hast mir wirklich sehr geholfen.

hast du vielleicht noch einen tip, wo/wie man sich gut und einfach über vb informieren kann, bzw. das lernen kann?

Matjes · 06.05.2004

hi kath.hundefriseursalon,

nix für , wie der Norddeutsche sagt.

bzgl lernen/informieren schau mal unter
http://www.wintotal-forum.de/?board=17;action=display;threadid=33407;start=msg212613#msg212613

Gruß Matjes

hans jupp · 10.05.2004

hi matjes,

ich kenn den thread, habe da ja auch schon gepostet

ich dachte du hättest vielleicht sonst noch einen heißen tip ...

trotzdem noch mal vielen dank

hans jupp · 10.05.2004

... und einen nachtrag hätte ich noch

ich hätte gerne, daß wenn der wert x in der abfrage vorkommt, daß dann die bestehende formartierung bleibt und nichts verändert wird.
leider habe ich in der hilfe nichts gefunden ...

ElseIf x = nix ändern :

Matjes · 10.05.2004

Hi Kath.hundefriseursalon,

ein If-Abfrage mehr:

Code:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
 ->die Konstanten schränken den überwachten Bereich ein:
 ->Spalte J, Zeile 4-34 - kann entsprechend abgeändert werden
  Const c_Spalte = 10->entspricht Spalte J
  Const c_Zeile_anf = 4
  Const c_Zeile_end = 34
 ->Spalte, die markiert werdensollen
  Const c_Sp_von = 1->entspricht Spalte A
  Const c_Sp_bis = 12->entspricht Spalte L
  Dim z As Range
  For Each z In Target
    If z.Column = c_Spalte Then
      If (c_Zeile_anf &lt;= z.Row) And (z.Row &lt;= c_Zeile_end) Then
       ->Zelle liegt im überwachten Bereich
       ->auf Wert prüfen und Zellhintergrund entspr. setzen
        With ActiveSheet.Range(Cells(z.Row, c_Sp_von), Cells(z.Row, c_Sp_bis))
          If x &lt;> z.Value Then-> x bleibt unverändert
            If 1 = z.Value Then->hellgelb
              .Interior.ColorIndex = 36
            ElseIf 2 = z.Value Then->hellrot
              .Interior.ColorIndex = 38
            ElseIf 3 = z.Value Then->hellblau
              .Interior.ColorIndex = 37
            ElseIf 4 = z.Value Then->hellgrün
              .Interior.ColorIndex = 35
            Else ->sonst
              .Interior.ColorIndex = xlColorIndexNone
            End If
          End If
        End With
      End If
    End If
  Next
End Sub

Gruß Matjes

hans jupp · 18.05.2004

hallo,

nochmal ein großes problem.

ich habe an deinem makro usw. etwas herumgespielt um das ganze mal auszuprobieren - seit dem funktioniert gar nichts mehr ...

daher habe ich das makro nochmals kopiert und eingefügt, aber es tut sich immer noch nichts.

gibt es irgendwo eine einstellung, wo man makro ausstellen kann o.ä.?

ich habe dummerweise keine ahnung mehr, was ich alles verstellt habe :

bedingte formartierung

hans jupp

Matjes

hans jupp

Matjes

hans jupp

Matjes

hans jupp

Matjes

hans jupp

hans jupp

Matjes

hans jupp

bedingte formartierung

ANGEBOTE & SPONSOREN

Neueste Themen

Statistik des Forums