Live-Forum - Die aktuellen Beiträge
Datum
Titel
26.09.2025 18:34:36
Welches Datum gibt mein M-Code aus?
26.09.2025 10:29:46
Wert aus einer Zelle bei jeder Eingabe in neue Zelle
26.09.2025 08:51:00
Teilinhalt einer Zelle vergleichen
25.09.2025 23:04:12
Einen von drei möglichen Einträgen in einer Zeile suchen
Keine passende Antwort im Forumarchiv gefunden?
Stelle deine Frage im Live-Forum
Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
352to356
352to356
Aktuelles Verzeichnis
Verzeichnis Übersicht
Verzeichnis Übersicht
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Bereiche "subtrahieren"

Forumthread: Bereiche "subtrahieren"

Bereiche "subtrahieren"
22.12.2003 15:21:50
Galenzo
Hallo Excel.Freunde,

ich möchte bei der Auswahl von Zellen mit Farbwechsel reagieren.
Dazu nutze ich das WorkSheet_SelectionChange-Ereignis und wechsle für alle Zellen, die innerhalb des festgelegten Bereiches liegen die Farbe

Dim rng As Range
Set rng = [F5:Z3000]
Intersect(Target, rng).Interior.ColorIndex = 33

Soweitsogut - das geht.

Nun möchte ich vom vorgegebenen Bereich aber jede 10. Zeile abziehen.
Die Zellen jeder 10. Zeile soll praktisch nicht umgefärbt werden, auch wenn sie markiert sind.

Konkret: Wie definiere ich einen Bereich, der über Spalten F bis Z alle Zeilen - außer den jeweils 10. Zeilen enthält.
Gibt es eine Art "Gegenstück" zu UNION, welches Bereiche voneinander abzieht?

Ich danke Euch schonmal für's lesen und freue mich auf Tips.

mfg
Anzeige

10
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Bereiche "subtrahieren"
22.12.2003 16:04:50
Beni
Hallo Galenzo,
probiere diesen Code.
Gruss Beni


Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Dim rng As Range
Set rng = [F5:Z3000]
For i = 10 To Target.Row Step 10
If Target.Row = i Then Exit Sub
Next i
If (Intersect(Target, rng) Is Nothing) Then Exit Sub
Intersect(Target, rng).Interior.ColorIndex = 33
End Sub

Anzeige
AW: Bereiche "subtrahieren"
22.12.2003 17:05:11
Boris
Hi Galenzo,

ich geh mal davon aus, dass du im Beispiel mit jeder 10. Zeile meinst:
Zeile 15,25,35 etc...

Dann vielleicht so (wobei Markierungen mit mehr als 10000 Zellen abgefangen werden, da es sonst zu lange dauert):

Option Explicit


Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
On Error Resume Next
Dim rng As Range, lRow As Long
Set rng = [F5:Z3000]
If Target.Cells.Count > 10000 Then
MsgBox "Bitte nicht so große Bereiche markieren", , "Gebe bekannt..."
Exit Sub
End If
For lRow = Target.Row To Target.Rows.Count
If (lRow + 5) Mod 10 <> 0 Then
Intersect(Rows(lRow), rng).Interior.ColorIndex = 3
Else
Intersect(Rows(lRow), rng).Interior.ColorIndex = xlNone
End If
Next lRow
End Sub


Grüße Boris
Anzeige
Kommando zurück...
22.12.2003 17:11:58
Boris
Hi Galenzo,

wenn überhaupt müsste es heissen:

For lRow = Target.Row To Target.Row + Target.Rows.Count

aber so wird immer der ganze Zeilenbereich F bis Z markiert - auch wenn nur eine Zelle ausgewählt wurde...

Muss nochmals überlegen - im Sinne einer einigermaßen vernünftigen Performance möchte ich nämlich nicht jede einzelne Zelle aus Target abfragen (was theoretisch natürlich möglich wäre).

Grüße Boris
Anzeige
Dann mit Intersect(Intersect(...))
22.12.2003 17:21:55
Boris
Hi Galenzo,

so geht´s - aber inzwischen bin ich mir nicht mehr sicher, ob es auch nicht viel einfacher geht...

Option Explicit


Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
On Error Resume Next
Dim rng As Range, lRow As Long
Set rng = [F5:Z3000]
If Target.Cells.Count > 10000 Then
MsgBox "Bitte nicht so große Bereiche markieren", , "Gebe bekannt..."
Exit Sub
End If
For lRow = Target.Row To Target.Row + Target.Rows.Count
If (lRow + 5) Mod 10 <> 0 Then
Intersect(Intersect(Rows(lRow), Target), rng).Interior.ColorIndex = 3
Else
Intersect(Intersect(Rows(lRow), Target), rng).Interior.ColorIndex = xlNone
End If
Next lRow
End Sub


Grüße Boris
Anzeige
AW: werd' ich mal probieren
22.12.2003 19:05:27
Galenzo
Hallo,
das mit Intersect(Intersect(.. müßte ich mal ausprobieren. Da bin ich ja noch garnicht drauf gekommen.
Das Durchlaufen des Bereiches ist leider keine passende Variante - ich brauche eben den Bereich.
(Um die jeweils 10. Zeilen zu kriegen reicht ja auch eine Prüfung der ganzzahligen Division auf den Rest 0 - also nur 10, 20, 30 etc sind ganzzahlig durch 10 teilbar.)

Vielen Dank für die Anregungen!
Anzeige
Und was prüfst du auf den ganzzahligen Rest?
22.12.2003 19:56:32
Boris
Hi Galenzo,

...doch wohl die Zeile - und die steckt ja hinter lRow (Target.Row). Also irgendwie musst du ja die Zeilen auslesen - und dafür musst du sie imho auch alle durchlaufen.

Grüße Boris
AW: Danke Boris.
23.12.2003 13:24:34
Galenzo
Hallo,
also Intersect(Intersect(.. geht nicht.

Erstmal Danke für deine Hilfe Boris. Ich werd' mir das nochmal genau durch den Kopf gehen lassen. Es wird wohl wie's aussieht kein Weg dran vorbeiführen, den markierten Bereich zu durchlaufen und (richtig!) die Zeilennummern zu prüfen.

Ich dachte nur, es wäre möglich, analog zu UNION mit einer Art Subtraktion einen Bereich zu definieren :-(

mfg
Anzeige
AW: Danke Boris - aber ich versteh dich nicht...
23.12.2003 13:50:19
Boris
Hi Galenzo,

mir soll´s ja eigentlich egal sein - aber du hast bisher kein Wort zu dem von mir geposteten Code verloren - denn der macht genau das, was du beschrieben hast.
Natürlich bleiben die Zeilen 15,25,35 etc... und nicht 10,20,30 ohne Farbe - aber das ist ja nur ne Miniänderung im Code:
Anstatt

If (lRow + 5) Mod 10 <> 0 Then

dann halt

If lRow Mod 10 <> 0 Then

Und schon bleiben die Zeilen 10,20,30 etc... ohne Farbe.
Oder ich hab es doch am Ende noch falsch verstanden?

Grüße Boris
Anzeige
AW: Bereiche "subtrahieren"
22.12.2003 23:44:07
Reinhard
Hi Galenzo,
nachfolgende Makros funktionieren.
Allerdings traten 2 Dinge auf, von denen ich nur eins glattgebügelt habe.

1) Wenn exakt eine Zelle markiert wird, so werden in 10 Zellen Formate reinkopiert, deshalb ist im Code die Abfrage:
If Bereich.Cells.Count <> 1 Then

2) Noch nicht korrigiert ist das Phänomen, dass, wenn mehrere Zellen im Grenzbereich von F5:Z3000 markiert werden und davon liegt exakt eine im Bereich F5:Z3000 dann werden auch die Zellen außerhalb des Bereichs F5:Z3000 mit formatiert.
Müßtest du ggfs noch abfangen.

Gruß
Reinhard

Sub NurEinmalNötig()
Set WS3 = ThisWorkbook.Worksheets("Tabelle3")
Set WS1 = ThisWorkbook.Worksheets("Tabelle1")
WS3.Range("A1:A9").Interior.ColorIndex = 33
WS3.Range("A10").Interior.ColorIndex = xlNone
WS1.Activate
Set WS3 = Nothing
Set WS1 = Nothing
End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Set Bereich = Application.Intersect(Target, Worksheets("Tabelle1").Range("F5:Z3000"))
If Bereich Is Nothing Then Exit Sub
If Bereich.Cells.Count <> 1 Then
ThisWorkbook.Worksheets("Tabelle3").Range("A1:A10").Copy
Bereich.PasteSpecial Paste:=xlFormats
Else
Target.Interior.ColorIndex = 33
End If
Set Bereich = Nothing
End Sub

Sub test()
ActiveSheet.Cells.Interior.ColorIndex = xlNone
End Sub


Anzeige
AW: Bereiche "subtrahieren"
23.12.2003 13:34:53
Galenzo
Danke Reinhard.

Das trifft's leider nicht ganz.
Ich habe mich in meiner Fragestellung vielleicht etwas ungenau ausgedrückt.

In meinem Worksheet_SelectionChange mache ich das:

Dim rng As Range
Set rng = [F5:Z3000]
Intersect(Target, rng).Interior.ColorIndex = 33

Ergebnis: ALLE Zellen meiner Auswahl, die IN dem Bereich rng liegen werden gefärbt.
Ich möchte aber, daß die Zeilen 10, 20, 30 usw. NICHT mit umgefärbt werden.
Ich dachte daran, dies gleich bei der Definition des "Prüf"-Bereiches rng zu berücksichtigen. Ich könnte ja schreiben:
set rng=([F5:Z9],[F11:Z19],[F21:Z24]..... usw.
Das würde es ja dann so tun. Problem: Tabelle nach unten offen.

Ich werde nun aber den umgekehrten Weg gehen: Erst färben, dann den Bereich durchlaufen und aus den 10er-Zeilen wieder die Farbe rausnehmen.

Danke - und ein Frohes Fest.
Anzeige
;
Anzeige

Infobox / Tutorial

Bereiche in Excel subtrahieren: Farbwechsel bei Zellauswahl

Schritt-für-Schritt-Anleitung

Um in Excel Bereiche zu subtrahieren und die Auswahl von Zellen mit einem Farbwechsel zu versehen, kannst Du das Worksheet_SelectionChange-Ereignis nutzen. Hier ist eine einfache Anleitung:

  1. Öffne den Visual Basic for Applications (VBA) Editor mit ALT + F11.
  2. Wähle das entsprechende Arbeitsblatt aus, in dem Du den Code einfügen möchtest.
  3. Füge den folgenden Code in das Codefenster ein:
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    Dim rng As Range
    Set rng = [F5:Z3000]

    ' Prüfen, ob die Zeile eine 10er-Zahl ist
    If (Target.Row Mod 10) <> 0 Then
        If Not Intersect(Target, rng) Is Nothing Then
            Intersect(Target, rng).Interior.ColorIndex = 33
        End If
    End If
End Sub

Dieser Code färbt die Zellen im Bereich F5:Z3000 nur dann, wenn die ausgewählte Zeile keine 10er-Zahl ist.

Häufige Fehler und Lösungen

  • Fehler: "Die Zellen werden nicht gefärbt."

    • Lösung: Stelle sicher, dass die Bedingung (Target.Row Mod 10) <> 0 korrekt überprüft wird. Wenn Du Zellen in einer 10er-Zeile auswählst, wird der Farbwechsel nicht ausgeführt.

  • Fehler: "Der Bereich ist zu groß."

    • Lösung: Füge eine Abfrage hinzu, die die Anzahl der markierten Zellen überprüft. Zum Beispiel:
If Target.Cells.Count > 10000 Then
    MsgBox "Bitte nicht so große Bereiche markieren"
    Exit Sub
End If

Alternative Methoden

Falls Du eine andere Methode zur Farbänderung von Zellen ausprobieren möchtest, kannst Du auch die Conditional Formatting-Funktion in Excel nutzen. Hierbei kannst Du eine Regel erstellen, die bestimmte Zeilen formatiert, basierend auf ihren Zeilennummern.

  1. Wähle den Bereich F5:Z3000 aus.
  2. Gehe zu Start > Bedingte Formatierung > Neue Regel.
  3. Wähle Formel zur Ermittlung der zu formatierenden Zellen verwenden und gib die Formel ein:
=REST(ZEILE();10)<>0
  1. Wähle das gewünschte Format aus und klicke auf OK.

Praktische Beispiele

Hier sind einige praktische Beispiele, wie Du den Code anpassen kannst:

  • Färbe die Zellen in einer anderen Farbe:

Ändere die ColorIndex-Eigenschaft in deinem Code:

Intersect(Target, rng).Interior.ColorIndex = 6 ' Gelb
  • Färbe auch bestimmte Spalten:

Wenn Du nur bestimmte Spalten färben möchtest, kannst Du die Intersect-Methode anpassen:

Dim rng As Range
Set rng = Union([F5:F3000], [H5:H3000]) ' Färbt nur Spalten F und H

Tipps für Profis

  • Verwende Option Explicit: Dies zwingt Dich, alle Variablen zu deklarieren, was hilft, Fehler zu vermeiden.
  • Optimierung der Performance: Vermeide das Durchlaufen von großen Bereichen, wenn es nicht notwendig ist. Nutze Intersect, um nur die relevanten Bereiche zu prüfen.
  • Verwendung von Application.EnableEvents: Setze diese Eigenschaft auf False, um das Ereignis während der Bearbeitung zu deaktivieren, und setze sie danach wieder auf True, um die Performance zu verbessern.
Application.EnableEvents = False
' Dein Code hier
Application.EnableEvents = True

FAQ: Häufige Fragen

1. Frage
Wie kann ich die Funktion in älteren Excel-Versionen verwenden?
Antwort: Der Code sollte in den meisten Excel-Versionen ab 2007 funktionieren. Wenn Du eine ältere Version verwendest, teste den Code, um sicherzustellen, dass alle Funktionen unterstützt werden.

2. Frage
Kann ich mehrere Bereiche subtrahieren?
Antwort: Ja, Du kannst die Union-Methode verwenden, um mehrere Bereiche zu kombinieren und dann mit Intersect zu arbeiten.

3. Frage
Wie kann ich die Zellen nach dem Färben zurücksetzen?
Antwort: Du kannst eine separate Subroutine erstellen, die die Interior.ColorIndex-Eigenschaft auf xlNone setzt, um die Farbe zu entfernen.

Beliebteste Forumthreads (12 Monate)

Jetzt das Excel-Portal bewerten: Deine Stimme zählt!
Anzeige
Anzeige
Entdecke mehr
Finde genau, was du suchst

Die erweiterte Suchfunktion hilft dir, gezielt die besten Antworten zu finden

Suche nach den besten Antworten
Unsere beliebtesten Threads

Entdecke unsere meistgeklickten Beiträge in der Google Suche

Top 100 Threads jetzt ansehen
Anzeige