Defining the cell that causes a Worksheet Change Event

[HJA14]

Hi all,

I have a question regarding the third answer on the following topic: https://stackoverflow.com/questions...-the-old-value-of-a-changed-cell-in-excel-vba

I have written the following macro:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim newPrice, oldPrice As String
    Dim cell, intersection As Range
    
    For Each cell In Target
        If Not Intersect(cell, Range("B:B")) Is Nothing Then
            newPrice = cel.Value
            oldPrice = ThisWorkbook.Worksheets("HiddenSheet").Range(cell.Address).Value
            Call Tick(oldPrice, newPrice)
        End If
    
    Next cell
    
    ThisWorkbook.Worksheets("HiddenSheet").UsedRange.Clear
    Me.UsedRange.Copy ThisWorkbook.Worksheets("HiddenSheet").Range(Me.UsedRange.Address)
    
End Sub

Q: I know how "Calling" works, but what does exactly the ..(oldPrice, newPrice)-part of the Calling? Are these values remembered when running the Tick-macro?

The Tick-macro does the following:

Sub Tick()    
    Dim newPrice, oldPrice As String
    Cells(Target.Row, 4).Value = oldPrice
    
    If Cells(Target.Row, Target.Column).Value < Cells(Target.Row, 4).Value Then
        Cells(Target.Row, 2).Select
            With Selection.Interior
                .Pattern = xlSolid
                .PatternColorIndex = xlAutomatic
                .Color = 10066431
                .TintAndShade = 0
                .PatternTintAndShade = 0
            End With
        Cells(Target.Row, 10).Value = Cells(Target.Row, 10).Value + 1
            
    Else
        Cells(Target.Row, 2).Select
            With Selection.Interior
                .Pattern = xlSolid
                .PatternColorIndex = xlAutomatic
                .Color = 11919289
                .TintAndShade = 0
                .PatternTintAndShade = 0
            End With
        Cells(Target.Row, 9).Value = Cells(Target.Row, 9).Value + 1
        
    End If


End Sub

However, how do I correctly replace the Target-part with the location of the cell from the "For Each cell In Target" in the previous macro?
For exampe, "Cells(Target.Row, 4).Value = oldPrice" should be rewritten to?

Thanks in advance,

 

[dmt32]

Q: I know how "Calling" works, but what does exactly the ..(oldPrice, newPrice)-part of the Calling? Are these values remembered when running the Tick-macro?

Your code is passing two arguments to your Tick procedure EXCEPT you have not declared the arguments in the procedure.

Also, you have declared your variables with different data types

Dim newPrice, oldPrice As String

newPrice is declared as variant (default) which is ok ish whilst oldPrice is declared as string.

I suspect you want Currency? if so, best to declare required data types to avoid errors

Dim newPrice As Currency, oldPrice As Currency

As you Tick code uses Range Target variable you will also need to pass to the code

Call Tick(Target, oldPrice, newPrice)

and your Update Tick Code

Sub Tick(ByVal Target As Range, ByVal newPrice As Currency, ByVal oldPrice As Currency)
    
    
    Cells(Target.Row, 4).Value = oldPrice
    
    If Cells(Target.Row, Target.Column).Value < Cells(Target.Row, 4).Value Then
        Cells(Target.Row, 2).Select
            With Selection.Interior
                .Pattern = xlSolid
                .PatternColorIndex = xlAutomatic
                .Color = 10066431
                .TintAndShade = 0
                .PatternTintAndShade = 0
            End With
        Cells(Target.Row, 10).Value = Cells(Target.Row, 10).Value + 1
            
    Else
        Cells(Target.Row, 2).Select
            With Selection.Interior
                .Pattern = xlSolid
                .PatternColorIndex = xlAutomatic
                .Color = 11919289
                .TintAndShade = 0
                .PatternTintAndShade = 0
            End With
        Cells(Target.Row, 9).Value = Cells(Target.Row, 9).Value + 1
        
    End If




End Sub

Hopefully, this will do what want.


dave