bed. Formatierung, wenn Formel in Zelle

Hi Wolfgang,

dann reicht dieser Code

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A1:F100")) Is Nothing Then Exit Sub
    ActiveSheet.Unprotect "Password"
    If Target.HasFormula Then
        Target.Interior.ColorIndex = 4
        Target.Locked = True
    End If
    ActiveSheet.Protect "Password"
End Sub

Den Else-Zweig benötigst du nun nicht mehr, da die gesperrten Zellen nicht wieder verändert werden können, ohne dass der gesamte Blattschutz aufgehoben wird.

 

Hi Karin,

Vielen Dank, ich habe mich nur leider etwas falsch ausgedrückt. Ich möchte alle farbig markierten Zellen in dem betreffenden Bereich sperren; es sind nämlich auch noch einige dabei, die durch eine bedingte Formatierung farblich hinterlegt sind, ohne dass sie eine Formel enthalten.

Noch ´ne Frage: Gibt es im Internet so eine Art "Wörterbuch", in dem die Begriffe wie "Target", "Intersect" usw. erklärt sind? Ich habe beim Googeln nichts gefunden, was mir wirklich weiterhilft.

Wolfgang

 

Hi Wolfgang,

die bedingte Formatierung auszulesen ist eine ziemlich komplizierte Angelegenheit - so ziemlich die komplizierteste die ich kenne. Dazu müsste ich wissen:
1. welche Zellen es betrifft
2. wie die Bedingungen heißen
Ich nehme an, die bedingte Formatierung bezieht sich auf eine direkte Eingabe in der Zelle und nicht auf ein Formelergebnis?

Zu den verschiedenen Begriffen findest du einiges in der VBA-Hilfe von Excel.

 

Hi Karin,

meine bedingten Formatierungen sind nicht abhängig von der direkten Eingabe in die Zelle, sondern von den Eingaben in anderen Zellen dieser Zeile bzw. auch von darunterliegenden Zeilen (alle 3 Bedingungen sind ausgeschöpft). Sie sind auch nicht auf einzelne Zellen beschränkt, sondern gehen ab einer bestimmten Spalte über den ganzen Rest der Tabelle.
Ich dachte, es wäre möglich, einfach den Farbcode der jeweiligen Zelle auszulesen und wenn er - wie z. B. in Deinem Code - 4 ist, den Sperrbefehl einzugeben.
Wenn das so kompliziert ist vergessen wir es. Du hast mir auch so schon sehr geholfen!!!

Wolfgang

 

Hi Wolfgang,

die Färbung mittels bedingter Formatierung lässt sich leider nicht wie die "normale" Färbung mittels Füllfarbe auslesen. Es hängt wirklich viel davon ab, wie die Bedingungen heißen. Man kann diese Bedingungen aber auch mittels VBA simulieren, dazu müsste ich jedoch die Zelladressen und die Bedingungen wissen.

 

Hi Karin,

dazu kann ich mich erst am Montag äußern, da ich auf die Tabellen vorher nicht zugreifen kann.

Wolfgang

 

Hallo Karin,

anbei ein Ausschnitt der Tabelle. Die bedingte Formatierung befindet sich in den Zellen der Spalten O und P und sie wird für jedes Jahr fortgesetzt.

Wolfgang

 

gelöscht da doppelt

 

Hi Wolfgang,

teste mal folgenden Code, ob er die richtigen Ergebnisse bringt, denn ich kenne die Bedingungen nicht so genau wie du. Du musst dazu aber die bedingten Formatierungen in O und P löschen, weil sonst diese wirken und nicht der Code (bedingte Formatierung hat immer den Vorrang vor Zellfärbung mit anderen Mitteln)

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column < 6 Then Exit Sub
'    ActiveSheet.Unprotect
    If Cells(Target.Row, 4) = "HV" And Cells(Target.Row, 3) <> Cells(Target.Row + 1, 3) And Left(Cells(Target.Row, 2), 1) <> "4" And Target.Offset(0, -1) <> "" Then
        Target.Interior.ColorIndex = 3
'        Target.Locked = True
    End If
    If Target.Offset(0, -1) = "" And Target.Offset(0, -2) = "" Or (Target.Offset(0, -1) = "aufgelöst" Or Target.Offset(0, -2) = "aufgelöst") Then
        If Cells(Target.Row, 6) <> "" And Cells(Target.Row, 2) <> 5612 Then
            Target.Interior.ColorIndex = 3
    '        Target.Locked = True
        End If
        If Cells(Target.Row, 6) = "" Then
            Target.Interior.ColorIndex = 3
    '        Target.Locked = True
        End If
    End If
'    ActiveSheet.Protect
End Sub

Der Code wirkt in allen Spalten ab F

 

Hallo Karin,

hat leider nicht hingehauen. Anbei beide Dateien.

Wolfgang

 

Hi Wolfgang,

??? Es war nur eine Datei in der Zip.

Es ist schwierig für mich, die bedingte Formatierung in VBA umzusetzen, da ich nicht nachvollziehen kann, wie deine Bedingungen wirken sollen. Beschreibe doch einfach mal ganz im Detail, wann die Zelle rot gefärbt werden soll.

 

Hi Hascheff,

danke für die Mühe, ich bin nicht so der Formelmensch. Leider können die Formelergebnisse nicht in die nächstfolgenden Spalten geschrieben werden und dann WAHR oder FALSCH ausgelesen werden, da die Tabelle nach rechts fortgeschrieben werden soll. Deshalb hatte ich Wolfgang gebeten, genau zu erklären, wann die Zelle rot gefärbt werden soll, so dass ich die Bedingungen verstehen und nachvollziehen kann.

 

Hallo Beverly und Hascheff,

vielen Dank für die Antworten.

Eine Zelle soll dann rot sein, wenn sie
a) in Spalte D als "HV" gekennzeichnet ist
b) in Spalte E der Zweck nicht identisch ist mit dem Zweck der darunterliegenden Zelle
c) die erste Ziffer in Spalte B keine "4" ist
d) für diesen Zweck im Vorjahr ein Datum eingetragen ist.

Weiterhin soll eine Zelle rot sein, wenn der Zweck im Lauf der letzten Jahre aufgelöst wurde und daher kein Datumseintrag mehr erfolgte. Dies gilt nicht für die Zeile, die in Spalte B den Eintrag "5612" hat; in dieser Zeile erscheint nie eine rote Zelle.

Das Ganze gilt erst ab dem Jahr 2007.

Das mit dem letzten Semikolon in Bedingung 3 stimmt. Mit Access habe ich leider nichts am Hut, so dass ich mich auf Excel beschränken muss.

mit den geänderten Formeln kann ich mich erst später beschäftigen; dazu brauche ich Ruhe.

Wolfgang

 

Hi Wolfgang,

ich habe mal den Code nach deiner Beschreibung geändert und auch entsprechende Kommentare eingefügt

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column < 6 Then Exit Sub
    ActiveSheet.Unprotect
[COLOR="Red"]'         Spalte D ="HV"    und    Spalte E aktuelle Zeile<>Spalte E aktuelle Zeile+1   und 1. Ziffer in Spalte B<>4             und aktuelle Spalte-1 nicht leer[/COLOR]
    If Cells(Target.Row, 4) = "HV" And Cells(Target.Row, 3) <> Cells(Target.Row + 1, 3) And Left(Cells(Target.Row, 2), 1) <> "4" And Target.Offset(0, -1) <> "" Then
        Target.Interior.ColorIndex = 3
'        Target.Locked = True
    End If
[COLOR="Red"]'      aktuelle Spalte-1=leer[/COLOR]
    If Target.Offset(0, -1) = "" Then
[COLOR="Red"]    '      Spalte B<>5612[/COLOR]
        If Cells(Target.Row, 2) <> 5612 Then
            Target.Interior.ColorIndex = 3
    '        Target.Locked = True
        End If
    End If
    ActiveSheet.Protect
End Sub

 

Versuche ZELLE(Infotyp; Bezug) in die bedingte Formatierung einzubauen.

 

Hi Beverly,

funkioniert leider auch nicht. Auf dem Tabellenblatt Kassenliste VBA-Code(2) müsste z.B. die Zelle P29 gesperrt und die Zelle P33 weiß sein.