Excel VBA: How to count cell and whenever cell change will keep the old and new data continuously

[aliffrusli]

Hi expertise,
I already enable worksheet change that will record every single data after click in specific cell. But, I have one problem onto count (preferably in range G:G) the cell within same row in multiple cell. Can anyone help me on this? Thank you.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.CountLarge > 1 Then Exit Sub
If Target.Column <> Range("F2:F100000").Column Then Exit Sub
Target.Offset(0, 2).Insert xlToRight
Target.Offset(0, 2).Value = Target.Value

End Sub

 

[Joe4]

Your question is quite confusing to me.

Can you walk me through an actual example, showing us:
1. what your current code does/is supposed to do
2. what you trying to do/add here

 

[aliffrusli]

Hi Joe4,
Please see my update as below. Thanks!!

1. Firstly, current code is whenever any of cell from "Due Date" get clicked, it will pop out calendar and user need to input their calendar. Upon clicked, Date_0 will capture the first date. If end user would like to revise the date, the same step repeated but the current date now is at Date_0 but the previous date will push to Date_1. If 10 times get clicked in "Due Date", We will have 10 date recorded from Date_0 till Date_10 (the code is as below). Secondly, "Due Date Change (#)" column will capture how many times the date input (I don't know how to do this).

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.CountLarge > 1 Then Exit Sub
If Target.Column <> Range("F2:F100000").Column Then Exit Sub
Target.Offset(0, 2).Insert xlToRight
Target.Offset(0, 2).Value = Target.Value

End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If (Target.Count = 1) Then
If Not Application.Intersect(Range("F2:F100000"), Target) Is Nothing Then UserForm1.Show
End If

End Sub

2. I want to add the the due date change count if possible within worksheet change code (not sure whether this is possible or not?).

Hopefully this will enlighten you question above

 

[Joe4]

Try using this for your "Worksheet_Change" code (note that you may want to add a line to format column H to your desired date format after inserting the new cells, after you have inserted/pasted in the new value).

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim lc As Long
    Dim rng As String

    If Target.CountLarge > 1 Then Exit Sub
    If Target.Column <> 6 Then Exit Sub
    
    If Target.Value > 0 Then
        Target.Offset(0, 2).Insert xlToRight
        Target.Offset(0, 2).Value = Target.Value
'       Find last column in row with data
        lc = Cells(Target.Row, Columns.Count).End(xlToLeft).Column
'       Build range to count
        rng = Range(Cells(Target.Row, 8), Cells(Target.Row, lc)).Address
'       Insert count function into column G
        Target.Offset(0, 1).Formula = "=COUNT(" & rng & ")"
    End If
    
End Sub