bed. Formatierung, wenn Formel in Zelle

Hallo zusammen,

noch ein kleines Excelproblemchen.
Ich habe in einem Vordruck eine Spalte mit manchmal mehreren hundert Einzelwerten, die aufaddiert werden müssen. Zwischendurch lasse ich mir immer Zwischenergebnisse =teilergebnis(9;xx:yy) anzeigen. Leider ist die Anzahl der Daten zwischen den einzelnen Zwischenergebnissen immer unterschiedlich, so dass ich keine festen Zellen für die Zwischenergebnisse markieren kann.
Ich möchte nun die Zellen mit den Zwischenergebnissen farblich hervorheben, d.h. ich habe versucht, über die bedingte Formatierung Excel dazu zu bringen, dass es eine Zelle farblich kennzeichnet, sobald die Formel "teilergebnis" darin steht - allerdings ohne Erfolg.
Hat jemand eine Idee? Wahrscheinlich geht´s mittels VBA-Programmierung, aber diesbezüglich bin ich ein völliger Laie.

Wolfgang

 

Dem ist nicht so. ich gebe jeweils nur die nackten Zahlen ein; die Zellen sind als Währungszellen formatiert, also brauche ich keine Beschriftung.

 

Die Teilsummenformel steht noch nicht da. Ich muss die Formel jedesmal neu schreiben, weil die Anzahl der summierten Zellen sich fast jedesmal ändert. Und weil das ganze ein geschütztes Formular ist, muss ich jedesmal den Schutz aufheben, um die Zelle "zu Fuß" einzufärben.

 

Hi Wolfgang,

das ließe sich mit VBA machen

Code:

Sub formelzelle_faerben()
    Dim loZeile As Long
    With Worksheets("Tabelle1")
        .Unprotect "Password"
        For loZeile = 1 To IIf(IsEmpty(.Cells(.Rows.Count, 1)), .Cells(.Rows.Count, 1).End(xlUp).Row, .Rows.Count)
            If .Cells(loZeile, 1).HasFormula Then
                .Cells(loZeile, 1).Interior.ColorIndex = 4
            Else
                .Cells(loZeile, 1).Interior.ColorIndex = xlNone
            End If
        Next loZeile
        .Protect "Password"
    End With
End Sub

Dieser Code gilt für Tabelle1 Spalte A, Formelzelle wird mit Füllfarbe Grün formatiert, Tabelle ist mit Passwort "Password" geschützt

 

Hi Hascheff,

simple Antwort

Man könnte auch gleichzeitig die Formel mit VBA eintragen, aber ich kenne den Tabellenaufbau nicht und weiß deshalb nicht, wie man feststellen kann, wo die Formel hin soll und für welchen Bereich die Teilsumme erstellt werden soll.

 

VBA ist immer sinnvoll, wenn man sich die Arbeit nicht auf einem einfachereren Weg erleichtern kann.

 

Hallo Beverly,

Dein VBA-Code ist genau das, was ich gesucht habe; Vielen Dank!
Eine Frage noch: Kann man in den Code noch einen "Schalter" einbauen, dass der Code jedesmal dann ausgeführt wird, wenn ich eine neue Zelle anspringe, sonst muss ich immer auf Makros und ausführen klicken.
Wolfgang

 

Hi Wolfgang,

natürlich kann man.

Kopiere folgenden Code in das Klasenmodul der Tabelle in der das Makro laufen soll

Code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Column <> 1 Then Exit Sub
    formelzelle_faerben
End Sub

Sobald du in eine beliebige Zelle der Spalte A deinen Cursor setzt, wird der Code ausgelöst.

 

Hi Beverly,

nachdem ich von VBA keine Ahnung habe: Was bitte ist das Klasenmodul?
Und welche Parameter muss ich ändern, damit ich nicht auf die Spalte A beschränkt bin?
Meine Tabelle sieht ungefähr so aus:

Spalte A: Einnahmen aus Vermietung
Spalte B: Einnahmen aus Verkäufen
...
Spalte H: Einzahlungen
Spalte I: Barausgaben
...

Für alle diese Ausgaben bzw. Einnahmen ziehe ich immer wieder Zwischensummen, die wegen der sofortigen Erkennbarkeit farblich abgesetzt sein sollen.

Wolfgang

 

Hi Wolfgang,

wenn du einen Rechtsklick auf den Tabellenreiter machst -> Code anzeigen, gelangst du in das Klassenmodul Tabelle. Nicht zu verwechseln mit dem allgemeinen Klassenmodul, welches man in der VBA-Umgebung über Einfügen einfügen kann und welches dazu da ist, um verschieden Objekte gemeinsam ansprechen bzw. programmierren zu können.

Um nur bestimmte Spalten anzusprechen, kann man eine If...ElseIf Anweisung oder auch eine Select Case Anweisung verwenden

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
    ActiveSheet.Unprotect "Password"
    Select Case Target.Column
        Case 1, 2, 8, 9
            If Target.HasFormula Then
                Target.Interior.ColorIndex = 4
            Else
                Target.Interior.ColorIndex = xlNone
            End If
    End Select
    ActiveSheet.Protect "Password"
End Sub

Ich habe den Code mal vom SelectionChange Ereignis in das Change Ereignis verlegt, da der Code ja eigentlich nur dann gestartet werden muss, falls ein Eintrag in die Zelle erfolgt, nicht bei jedem Klick in eine Zelle. Teste mal, ob es so funktionert, wie du es dir vorstellst. Das Modul und den Code im SelectionChange Ereignis natürlich entfernen.

 

Hallo Beverly,

Sorry, dass ich mich erst jetzt melde, aber ich hatte gestern Abend und heute einfach keine Zeit. Die Tests, die ich gerade eben durchgeführt habe, bringen genau das Ergebnis, das ich haben will.
Ich werde über die Feiertage versuchen, ein wenig mit dem Code herumzuspielen (andere Farben, ganzes Tabellenblatt).
Falls ich Hilfe brauche, kann ich mich nochmal melden?
Einstweilen Vielen Dank und schöne Feiertage!

Wolfgang

 

Hi Wolfgang,

natürlich darfst du dich jederzeit wieder melden, wenn du noch Fragen hast. Und dass man nicht immer sofort Zeit hat, ist doch auch verständlich. Dann wünsche ich dir viel Erfolg beim Testen und natürlich ebenfalls schöne Feiertage.

 

Hallo Beverly,

ich habe es geschafft, einen zusammenhängenden Spaltenbereich, sämtliche Spalten eines Tabellenblattes und anstatt der Spalten die Farbänderung in Zeilen vorzunehmen. Eine Farbcodetabelle habe ich inzwischen auch.
Woran ich mir die Zähne ausgebissen habe, ist eine Kombination von Spalten und Zeilen, also z.B. der Bereich A1:F100. Wenn Du mir da bitte nochmal unter die Arme greifen könntest.

Wolfgang

 

Hi Wolfgang,

klar, tue ich doch gern.

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
    Else
        Target.Interior.ColorIndex = xlNone
    End If
    ActiveSheet.Protect "Password"
End Sub

 

Vielen Dank Beverly!

Funktioniert genauso, wie ich es haben möchte.

Wolfgang

 

Mittlerweile hat sich noch eine Frage ergeben:

Wie müsste ich die Programmierung ergänzen, wenn die farblich markierten Zellen gleichzeitig gesperrt werden sollen (sie befinden sich innerhalb eines Bereiches mit lauter nicht gesperrten Zellen)?

Wolfgang