A way to update everytime a cell is updated (based on days)

[vimsikalbadtard]

Hello... so i have this excel database with me which shows the discount given on all the products in the market. Initially the discount of lets say product x is -12%... It did not sell.. 15 days later i change it to -18%... it still didn't sell... and i keep changing and giving a higher discount till its sold.
My request here is that is there a way to find out how many times the particular cell has been updated? preferably in days.. for eg: product x was updated 10 times before it was sold so the cell should say 10. But, the count will go up only once in one day regardless of you changing it 8 times in a day.. final count will be 1 for that day.I was able to come up with this code however it updated it every time i inserted a new value and that would lead to 10-12 times a day instead of 1.

Dim xCount As Integer
 
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim xRg As Range, xCell As Range
    On Error Resume Next
    If Target = Range("B:B") Then
        xCount = xCount + 1
        Range("C:C").Value = xCount                                   
    End If
    Application.EnableEvents = False
    Set xRg = Application.Intersect(Target.Dependents, Me.Range("B:B"))
    If Not xRg Is Nothing Then
        xCount = xCount + 1
        Range("C:C").Value = xCount
    End If
    Application.EnableEvents = True
End Sub

[/CODE]

The above code was given by a friend. I dont know much about it.
I know this is a really hard request but please help me out. (Im not that good with VBA, sorry in advance)

 

[Peter_SSs]

Welcome to the MrExcel board!

Hmm, this code looks quite familiar ..
Automatic add Time and Date
How to auto populate date in cell when adjacent cell is updated in Excel?

how many times the particular cell has been updated?

What cell is the "particular cell"?

 

[vimsikalbadtard]

Welcome to the MrExcel board!

Hmm, this code looks quite familiar ..
Automatic add Time and Date
How to auto populate date in cell when adjacent cell is updated in Excel?


What cell is the "particular cell"?

The one with the changing discounts

 

[Peter_SSs]

The one with the changing discounts

You may know which one that it, but I don't.

Although your description referred to a "particular cell" perhaps you actually meant any cell in the column?
If so, is that column B with the changing discounts?

 

[vimsikalbadtard]

You may know which one that it, but I don't.

Although your description referred to a "particular cell" perhaps you actually meant any cell in the column?
If so, is that column B with the changing discounts?

extremely sorry for the confusion. Yes it will be colB.. Particular cell was an example. The whole columnB will be having changes in prices and maybe colC will show how many changes made daywise?

 

[Peter_SSs]

Try this Worksheet change code instead of the above. It uses column Z to record what date the column B cell in that row was last changed so that you don't get multiple increments on the same day.
That can be any column you want and could be hidden.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Changed As Range, c As Range
  
  Const DateCol As String = "Z"
  
  Set Changed = Intersect(Target, Columns("B"), Rows("2:" & Rows.Count))
  If Not Changed Is Nothing Then
    Application.EnableEvents = False
    For Each c In Changed
      With c
        If Not IsEmpty(.Value) And Range(DateCol & .Row).Value <> Date Then
          .Offset(, 1).Value = .Offset(, 1).Value + 1
          Range(DateCol & .Row).Value = Date
        End If
      End With
    Next c
    Application.EnableEvents = True
  End If
End Sub

 

[vimsikalbadtard]

Try this Worksheet change code instead of the above. It uses column Z to record what date the column B cell in that row was last changed so that you don't get multiple increments on the same day.
That can be any column you want and could be hidden.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Changed As Range, c As Range
 
  Const DateCol As String = "Z"
 
  Set Changed = Intersect(Target, Columns("B"), Rows("2:" & Rows.Count))
  If Not Changed Is Nothing Then
    Application.EnableEvents = False
    For Each c In Changed
      With c
        If Not IsEmpty(.Value) And Range(DateCol & .Row).Value <> Date Then
          .Offset(, 1).Value = .Offset(, 1).Value + 1
          Range(DateCol & .Row).Value = Date
        End If
      End With
    Next c
    Application.EnableEvents = True
  End If
End Sub

This works perfectly... Thank you so much! hope you have a wonderful day

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.