Hi,

I need a cell (U) to display the current date (and keep it) everytime i
change something in row W.
I found the following code:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count 1 Then Exit Sub
If Not Intersect(Range("W2:W9999"), .Cells) Is Nothing Then
Application.EnableEvents = False
With .Offset(0, 1)
.NumberFormat = "dd mmm yyyy"
.Value = Date
End With
Application.EnableEvents = True
End If
End With
End Sub

I've got one main question, can i change this formula to also activate when
a formula value changes? (Let's say W2 is IF(A1="";"Empty";"Not Empty")

Now when i enter something in A1, W2 should change from Empty to Not Empty.
The Date Stamp code, however, does not see this as a change in the cell.. Is
there any way to solve this?

Greets & Thanks,
gunti

Target is A1 when change is made in A1... and your code runs only when range
is W2:W9999

One way is to check for Range("A1") also...
or have a separate IF for A1 just like the one you already have...

"Gunti" wrote:

Hi,

I need a cell (U) to display the current date (and keep it) everytime i
change something in row W.
I found the following code:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count 1 Then Exit Sub
If Not Intersect(Range("W2:W9999"), .Cells) Is Nothing Then
Application.EnableEvents = False
With .Offset(0, 1)
.NumberFormat = "dd mmm yyyy"
.Value = Date
End With
Application.EnableEvents = True
End If
End With
End Sub

I've got one main question, can i change this formula to also activate when
a formula value changes? (Let's say W2 is IF(A1="";"Empty";"Not Empty")

Now when i enter something in A1, W2 should change from Empty to Not Empty.
The Date Stamp code, however, does not see this as a change in the cell.. Is
there any way to solve this?

Greets & Thanks,
gunti

It's not an option to let the macro check ~ 12 or more ranges (I just made a
simplified example). It has to detect a change in the formula value.

Thanks though,
Gunti

"Sheeloo" wrote:

Target is A1 when change is made in A1... and your code runs only when range
is W2:W9999

One way is to check for Range("A1") also...
or have a separate IF for A1 just like the one you already have...

"Gunti" wrote:

Hi,

I need a cell (U) to display the current date (and keep it) everytime i
change something in row W.
I found the following code:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count 1 Then Exit Sub
If Not Intersect(Range("W2:W9999"), .Cells) Is Nothing Then
Application.EnableEvents = False
With .Offset(0, 1)
.NumberFormat = "dd mmm yyyy"
.Value = Date
End With
Application.EnableEvents = True
End If
End With
End Sub

I've got one main question, can i change this formula to also activate when
a formula value changes? (Let's say W2 is IF(A1="";"Empty";"Not Empty")

Now when i enter something in A1, W2 should change from Empty to Not Empty.
The Date Stamp code, however, does not see this as a change in the cell.. Is
there any way to solve this?

Greets & Thanks,
gunti