Worksheet change event 1

[Lodebo]

Hi,
The range in this code contains formulas.
When the values change the code does not work.
I know it has something to do with the formulas but i can't find out what to do.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Me.Range("E6:E31")) Is Nothing Then
     Cells(Target.Row, Target.Column + 3) = Date
  End If
    
End Sub

 

[SkipVought]

Hi,

A formula calculation a value is not a change.

If you were to modify the formula, THAT'S a change thst will be trapped by the Worksheet Change event.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Lodebo]

Thanks Skip,

So there is no solution ?
Hmmmm.....hard to believe

Lode.

 

[SkipVought]

Please explain what you need to do.

Please do not merely state what you want your code to do. I want to understand WHY you want a date in the row, which may mean that you need to explain what your formulas are doing and what else is happening on the sheet that your formulas reference.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Lodebo]

Thanks again Skip.

The formula is the sum of some cells in another sheet.
When the cell that contains the formula changes, then in the next column and same row i want the present date.
That's all...

 

[SkipVought]

So how do the values on the the other sheet get changed?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Lodebo]

Manually

 

[SkipVought]

So when any cell is changed in the range in that other sheet, that is summed on the original sheet in E6:E31, that change event is what you need to capture.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[combo]

Not tested for stability in all situations, but you could use UDF:

[tt]Public Function X(r As Range) As Date
X = Now()
End Function[/tt]

Use it in the next column, with tested cell containing formula as argument. You can format as date and time. Changed input value causes UDF recalculation resulting calculation time as output.

combo

 

[Lodebo]

Skip,

Spot on, that's what i need.

Combo,

I don't understand how to implement this.

thx guys

 

[combo]

It's an UDF function that recalculates when entry value changes and returns current time.
So in worksheet:
cell A1:
formula: 1, value: 1
cell B1:
formula 2, value: 2
cell C1:
formula: =A1+B1, value: 3
cell D1:
formula; =X(C1), value: entry date and time
Now, changing A1 or B1 causes change of C1 and this triggers recalculation of D1, you have new time in D1. For me, F9, saving and opening the workbook does not force to recalculate D1 (however, when you reenter D1, its value changes).
C1: =A1+

combo

 

[Lodebo]

Combo,

Thank you so much....
This works perfect !!!!!

Best regards,
Lode.

 

[combo]

Thanks. In case of any problems, Skip's tip to trace source data is more robust.

combo