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

Djack92 · Oct 21st 2021

Hello,

With the help of the people from this forum, we arrived at the bellow code, now I faced a different challenge I would like the code to log the change of the cell, like is doing with the bellow code just in case of a specific change (example in column 4 I choose from drop-down list NO, YES, MAYBE ) the VBA should not count them as a change, but any other value should be counted towards the changes counter.

Any idea or hint will be welcomed.
P.S Big thx to the people that helped me last time.

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

**royUK** · Oct 21st 2021

Does the cell have a Data Validation List? If it does you cannot make any other entries except from the list.

Djack92 · Oct 21st 2021

Hello royUk,

Yes, the cell has Data Validation List (in the list I have NO, YES, MAYBE, and another 4 options ).

So basically i want to remove these 3 options from counting and to be able to count only the other 4 options.

**royUK** · Oct 21st 2021

This should do that

Code

Private Sub Worksheet_Change(ByVal Target As Range)
    With Target
        If Not .Column = 4 Or .CountLarge > 1 Then Exit Sub
        Application.EnableEvents = False
        Select Case .Value
            Case "YES", "NO", "MAYBE"
                ''/// do nothing
            Case Else: Offset(, 1).Value = .Offset(, 1).Value + 1
        End Select
    End With
    Application.EnableEvents = True
End Sub

Display More

You could save the count to a Note(Comment) if you don't need it in the table or hide the count column.

Djack92 · Oct 21st 2021

Hi royUK,

I tried to run it but I got compile error: Sub or Function not defined.
tried to figure out the issue and can not see the problem.

**royUK** · Oct 21st 2021

It's not a big change to your code, attach your workbook so that I can see what's happening.

Djack92 · Oct 22nd 2021

Hello royUK,

Sorry for the late reply.

I attached a sample of the file

Best Regards,

**royUK** · Oct 22nd 2021

I've corrected the code.

Code

Private Sub Worksheet_Change(ByVal Target As Range)
    With Target
        If Not .Column = 4 Or .CountLarge > 1 Then Exit Sub
        Application.EnableEvents = False
        Select Case .Value
            Case "YES", "NO", "MAYBE"
                ''/// do nothing
            Case Else: .Offset(, 1).Value = .Offset(, 1).Value + 1
        End Select
    End With
    Application.EnableEvents = True
End Sub

Display More

Djack92 · Oct 25th 2021

Hello royUK,

Sorry for the late reply, the code is working
Thank you a lot for the help.

**royUK** · Oct 25th 2021

Pleased to help.

Post back if you need further help.

Visit my web site, http://www.excel-it.com, for more examples and some helpful articles.

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

Participate now!

Share

Tags