Automatically trigger code when formulas change

[dell_12345]

Hi, Iv got some very basic VBA coding that will copy a cell value to another cell every time the original cell value changes. However this only works when I manually change the cell value. I want this to change automatically as it is linked to another sheet which is changing constantly. Below is my code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim lRow As Long
Dim vaData() As Variant
If Intersect(Target, Range("B1")) Is Nothing Then Exit Sub


ReDim vaData(1 To 1, 1 To 3)
vaData(1, 1) = Now()
vaData(1, 2) = Target.Address
vaData(1, 3) = Target.Resize(1, 1).Value
Application.EnableEvents = False
With Sheets("Sheet2")
    lRow = .Cells(Rows.Count, "A").End(xlUp).Row + 1
    .Range("A" & lRow & ":C" & lRow).Value = vaData
End With
Application.EnableEvents = True
End Sub

People are saying use the Worksheet_Calculate function but I have no idea how to implement this in the above code.

Greatful for any help!!!!

 

[RoryA]

Do you still want the date and cell address logged?

 

[dell_12345]

Yes preferably I would but if its a major problem it can be taken out.

 

[dell_12345]

Do you still want the date and cell address logged?

The Cell reference can me taken out to be fair as that has little use.

 

[RoryA]

In that case try this:

Private Sub Worksheet_Calculate()
Dim lRow As Long
Dim vaData() As Variant
Dim Target As Range


Set Target = Range("G36")


If CStr(Target.Value) <> Target.ID Then
Target.ID = CStr(Target.Value)
ReDim vaData(1 To 1, 1 To 4)
vaData(1, 1) = Now()
vaData(1, 2) = Target.Value
vaData(1, 3) = Target.Offset(, 1).Value
vaData(1, 4) = Target.Offset(, 2).Value
Application.EnableEvents = False
With Sheets("Sheet2")
    lRow = .Cells(Rows.Count, "A").End(xlUp).Row + 1
    .Range("A" & lRow).Resize(1, ubound(vaData, 2)).Value = vaData
End With
Application.EnableEvents = True
End If
End Sub

 

[dell_12345]

In that case try this:

Private Sub Worksheet_Calculate()
Dim lRow As Long
Dim vaData() As Variant
Dim Target As Range


Set Target = Range("G36")


If CStr(Target.Value) <> Target.ID Then
Target.ID = CStr(Target.Value)
ReDim vaData(1 To 1, 1 To 4)
vaData(1, 1) = Now()
vaData(1, 2) = Target.Value
vaData(1, 3) = Target.Offset(, 1).Value
vaData(1, 4) = Target.Offset(, 2).Value
Application.EnableEvents = False
With Sheets("Sheet2")
    lRow = .Cells(Rows.Count, "A").End(xlUp).Row + 1
    .Range("A" & lRow).Resize(1, ubound(vaData, 2).Value = vaData
End With
Application.EnableEvents = True
End If
End Sub

Thankyou for your time and patience Rory. It says there is a syntax error.

The below code is highlighted in red.
.Range("A" & lRow).Resize(1, ubound(vaData, 2).Value = vaData

 

[RoryA]

Sorry - dangers of air code. That should be:

 .Range("A" & lRow).Resize(1, ubound(vaData, 2)).Value = vaData

 

[dell_12345]

Sorry - dangers of air code. That should be:

 .Range("A" & lRow).Resize(1, ubound(vaData, 2)).Value = vaData

Hi Rory that has got rid of the cell reference which is perfect however I want it to record a number of cells when that target cell value changes. My example wasn't very clear earlier but I want to record the value of cells I4:I34 in separate columns when the target value changes. Again thankyou very much for this.

 

[dell_12345]

Just thought I would bump the post as still struggling with this.

 

[RoryA]

Just I4:I34, or the date and the value of G36 as well?

 

[dell_12345]

Just I4:I34, or the date and the value of G36 as well?

All of it if possible. Preferably I want the the date in column a, value of target cell 'G36' in column b, the I4 in c, I5 in d, I6 in e ect.
It may be necessary to use another sheet to fit this on a bit better.

regards