Count of each change made in a cell to another cell for entire column

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

    Hello,

    I need a function that records the number of times a cell in a worksheet has a value change.
    I got it, how to count for 1 cell but my brain just can not crack how to make it work for the entire column.
    So as an example :
    I have a value in D8 and need to record it in E8
    and so on for the entire column D9 to E9
    I'm really new to all this code stuff so help would be very much appreciated.

    P.S only my solution, for now, is to do it for each cell in particular like this:


    Code
    If Not Intersect(Target, Range("D8")) Is Nothing Then
Range("E8").Value = Range("E8").Value + 1
End If
If Not Intersect(Target, Range("D9")) Is Nothing Then
Range("E9").Value = Range("E9").Value + 1
End If

    Welcome to the Forum. Please read the Forum Rules to understand how the Forum works and why I have added Code Tags to your post


    All VBA code posted in the forum must be wrapped in code tags, which you omitted, including single-line code snippets.Be sure to use them in future posts.


    How to use code tags


    Just highlight all of the code and press the <> in the post menu above button to add the code tags.


    Thanks.

    You could check an entire column using something like this:


    Code
    Dim Cell as Range
If not intersect(target, range("D:D")) is nothing then
application.enableevents = false
for each cell in intersect(target, range("D:D")).Cells
cell.offset(, 1).value = Cell.Offset(, 1).value + 1
next cell
application.enableevents = true
end if

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

    Try this


    Code
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Target.Column = 4 Then Exit Sub
    Application.EnableEvents = False
    Target.Offset(, 1).Value = Target.Offset(, 1).Value + 1
    Application.EnableEvents = True
End Sub

    It will probably be better to make sure the code only works on single cells as well as checking it is in Column 4


    Code
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Target.Column = 4 Or Target.CountLarge > 1 Then Exit Sub
    Application.EnableEvents = False
    Target.Offset(, 1).Value = Target.Offset(, 1).Value + 1
    Application.EnableEvents = True
End Sub

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login