so zum beispiel
02.06.2017 11:48:12
Max2
Hallo,
hier Mappe: https://www.herber.de/bbs/user/113982.xlsm
hier Code:
Option Explicit
Private Sub Worksheet_Change(ByVal target As Range)
Dim search As String
Dim i As Long, j As Long
On Error Resume Next
If target.Column = 3 Then
Application.EnableEvents = False
For i = 22 To Cells(Rows.Count, 2).End(xlUp).Row
search = Cells(i, 2).Value
Call count_values(search, j)
Cells(i, 3).Value = j
Next i
Call change_color
Application.EnableEvents = True
End If
End Sub
'Kann man auch in eine Function abändern
Private Sub count_values(ByVal countMe As String, ByRef amount As Long)
Dim rng As Range, c
Dim counter As Long
Dim ws As Worksheet
Dim firstAddress
On Error Resume Next
Set ws = ThisWorkbook.Sheets("Zaehlwerk")
With ws
Set rng = .Range(.Cells(.Rows.Count, 3), .Cells(1, 3))
With rng
Set c = .Find(countMe, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
counter = counter + 1
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address firstAddress
End If
End With
End With
amount = counter
End Sub
Private Sub change_color()
Dim ws As Worksheet
Dim i As Long, j As Long
Dim soll As Integer, ist As Integer
Set ws = ThisWorkbook.Sheets("Zaehlwerk")
With ws
i = .Cells(.Rows.Count, 2).End(xlUp).Row
For j = 22 To i
soll = .Cells(j, 1).Value
ist = .Cells(j, 3).Value
If ist > soll Then
.Cells(j, 3).Interior.ColorIndex = 3
Else
.Cells(j, 3).Interior.ColorIndex = 4
End If
Next j
End With
End Sub
Wenn es wirklich bei jeder änderung ausgeführt werden soll, dann musst du "If target.Column = 3" rausnehmen.