Änderung der Zellenfarbe

[JonasRe]

Hallo,
ich habe folgendes Problem. Ich möchte, dass sich die Zellenfärbe ändert, wenn der Wert sich innerhalb einer Zelle verändert. Jetzt habe ich einen Button erstellt, der verschiedene Spalten summiert in einer Spalte. Wenn ich diesen Button drücke ändert sich jetzt aber die komplette Spalte, die die Summe abbildet und nicht nur die eine Zelle, deren Wert sich verändert hat.
Das heißt, ich bräuchte eine Überprüfung, ob sich wirklich nur der Wert der Zelle verändert hat. Beziehungsweise, besteht eine elegante Lösung, dass die Berechnung der Summe auch zeilenweise erfolgt?

Momentan sieht mein Code folgendermaßen aus, welcher die Zellenfarbe ändern soll:

Code:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("M3:AB3000")) Is Nothing Then
   
    Target.Interior.ColorIndex = 6
End If
End Sub


Und der Code zur Ermittlung der Summe:


Sub platzhalter()
Dim instring As String
Dim outstring() As String
Dim book, mybook As Workbook
Dim sourceline, destinationline As Integer
Dim Result As Integer
Dim Pfad As String
Dim Help1 As Integer
Dim Help2 As Integer
Dim Help3 As Integer
Dim Help4 As Integer
Dim Help5 As Integer
Dim Help6 As Integer
Dim Summe As Integer


Set mybook = ActiveWorkbook
destinationline = 3
sourceline = 3
While (mybook.Worksheets("Gehaltsdaten").Cells(destinationline, 1) <> "")
With mybook.Worksheets("Gehaltsdaten")
If .Cells(sourceline, 20) = "" Then GoTo Sprung
If .Cells(sourceline, 20) = "A" Then Help1 = 0
If .Cells(sourceline, 20) = "B" Then Help1 = 2
If .Cells(sourceline, 20) = "C" Then Help1 = 4
If .Cells(sourceline, 20) = "D" Then Help1 = 6
If .Cells(sourceline, 20) = "E" Then Help1 = 8
If .Cells(sourceline, 21) = "A" Then Help2 = 0
If .Cells(sourceline, 21) = "B" Then Help2 = 2
If .Cells(sourceline, 21) = "C" Then Help2 = 4
If .Cells(sourceline, 21) = "D" Then Help2 = 6
If .Cells(sourceline, 21) = "E" Then Help2 = 8
If .Cells(sourceline, 22) = "A" Then Help3 = 0
If .Cells(sourceline, 22) = "B" Then Help3 = 1
If .Cells(sourceline, 22) = "C" Then Help3 = 2
If .Cells(sourceline, 22) = "D" Then Help3 = 3
If .Cells(sourceline, 22) = "E" Then Help3 = 4
If .Cells(sourceline, 23) = "A" Then Help4 = 0
If .Cells(sourceline, 23) = "B" Then Help4 = 1
If .Cells(sourceline, 23) = "C" Then Help4 = 2
If .Cells(sourceline, 23) = "D" Then Help4 = 3
If .Cells(sourceline, 23) = "E" Then Help4 = 4
If .Cells(sourceline, 24) = "A" Then Help5 = 0
If .Cells(sourceline, 24) = "B" Then Help5 = 1
If .Cells(sourceline, 24) = "C" Then Help5 = 2
If .Cells(sourceline, 24) = "D" Then Help5 = 3
If .Cells(sourceline, 24) = "E" Then Help5 = 4
If .Cells(sourceline, 25) = "A" Then Help6 = 0
If .Cells(sourceline, 25) = "B" Then Help6 = 1
If .Cells(sourceline, 25) = "C" Then Help6 = 2
If .Cells(sourceline, 25) = "D" Then Help6 = 3
If .Cells(sourceline, 25) = "E" Then Help6 = 4
Summe = Help1 + Help2 + Help3 + Help4 + Help5 + Help6
.Cells(destinationline, 19) = Summe
.Cells(destinationline, 39) = Summe
End With
Sprung:
destinationline = destinationline + 1
sourceline = sourceline + 1
Help1 = 0
Help2 = 0
Help3 = 0
Help4 = 0
Help5 = 0
Help6 = 0
Wend

End Sub

Ich danke euch im voraus!

[Steffl]

Hallo,

den Sinn von deinem Makro verstehe ich zwar nicht, aber versuche es mal so

Code:

Sub platzhalter()
Dim instring As String
Dim outstring() As String
Dim book, mybook As Workbook
Dim sourceline, destinationline As Integer
Dim Result As Integer
Dim Pfad As String
Dim Help1 As Integer
Dim Help2 As Integer
Dim Help3 As Integer
Dim Help4 As Integer
Dim Help5 As Integer
Dim Help6 As Integer
Dim Summe As Integer

Application.EnableEvents = False
Set mybook = ActiveWorkbook
destinationline = 3
sourceline = 3
While (mybook.Worksheets("Gehaltsdaten").Cells(destinationline, 1) <> "")
With mybook.Worksheets("Gehaltsdaten")
If .Cells(sourceline, 20) = "" Then GoTo Sprung
If .Cells(sourceline, 20) = "A" Then Help1 = 0
If .Cells(sourceline, 20) = "B" Then Help1 = 2
If .Cells(sourceline, 20) = "C" Then Help1 = 4
If .Cells(sourceline, 20) = "D" Then Help1 = 6
If .Cells(sourceline, 20) = "E" Then Help1 = 8
If .Cells(sourceline, 21) = "A" Then Help2 = 0
If .Cells(sourceline, 21) = "B" Then Help2 = 2
If .Cells(sourceline, 21) = "C" Then Help2 = 4
If .Cells(sourceline, 21) = "D" Then Help2 = 6
If .Cells(sourceline, 21) = "E" Then Help2 = 8
If .Cells(sourceline, 22) = "A" Then Help3 = 0
If .Cells(sourceline, 22) = "B" Then Help3 = 1
If .Cells(sourceline, 22) = "C" Then Help3 = 2
If .Cells(sourceline, 22) = "D" Then Help3 = 3
If .Cells(sourceline, 22) = "E" Then Help3 = 4
If .Cells(sourceline, 23) = "A" Then Help4 = 0
If .Cells(sourceline, 23) = "B" Then Help4 = 1
If .Cells(sourceline, 23) = "C" Then Help4 = 2
If .Cells(sourceline, 23) = "D" Then Help4 = 3
If .Cells(sourceline, 23) = "E" Then Help4 = 4
If .Cells(sourceline, 24) = "A" Then Help5 = 0
If .Cells(sourceline, 24) = "B" Then Help5 = 1
If .Cells(sourceline, 24) = "C" Then Help5 = 2
If .Cells(sourceline, 24) = "D" Then Help5 = 3
If .Cells(sourceline, 24) = "E" Then Help5 = 4
If .Cells(sourceline, 25) = "A" Then Help6 = 0
If .Cells(sourceline, 25) = "B" Then Help6 = 1
If .Cells(sourceline, 25) = "C" Then Help6 = 2
If .Cells(sourceline, 25) = "D" Then Help6 = 3
If .Cells(sourceline, 25) = "E" Then Help6 = 4
Summe = Help1 + Help2 + Help3 + Help4 + Help5 + Help6
.Cells(destinationline, 19) = Summe
.Cells(destinationline, 39) = Summe
End With
Sprung:
destinationline = destinationline + 1
sourceline = sourceline + 1
Help1 = 0
Help2 = 0
Help3 = 0
Help4 = 0
Help5 = 0
Help6 = 0
Wend
Application.EnableEvents = True
End Sub