Date Event

[gregork]

I have the following formula on my worksheet that I was going to use to
record a date when a stock level is reached:

IF($I3>=$H3,TODAY(),"INCOMPLETE")

Of course its not gunna work because after all 'tomorrows another day' so my
formula is going to return a different date.
What I need is the exact day that I3 =H3 to be noted so I guess I need an
event right?
I also need it to work on a range of cells. Not sure how to go about this.

GK

 

[Bob Phillips]

Gregor,

Here is some event code. It works if any cell in A1:A100 is selected and
then does that compariosn.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
With Target
If Not Intersect(Target, Me.Range("A1:A100")) Is Nothing Then
If Me.Cells(.Row, "I").Value = Me.Cells(.Row, "H").Value Then
.Value = Date
.NumberFormat = "dd mmm yyyy"
Else
.Value = "INCOMPLETE"
.NumberFormat = "@"
End If
End If
End With

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[gregork]

Thanks for the help Bob. Here's what I have done to the code to suit my
needs . It works but only if I click on a cell to activate it ("K1:K1000").
Can the code be changed so I don't have to click on it to get the return?
The cells with the value that changes is I1:I1000 . Can the date value be
offset from this range so that it ends up in K1:K1000?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
With Target
If Not Intersect(Target, Me.Range("K1:K1000")) Is Nothing Then
If Me.Cells(.Row, "I").Value <= 0 Then


.Value = Date
.NumberFormat = "dd mmm yyyy"
Else
.Value = "INCOMPLETE"
.NumberFormat = "@"
End If
End If
End With

Many thanks for help
GK

ws_exit:
Application.EnableEvents = True
End Sub

 

[Bob Phillips]

Gregor,

You need something to happen to force the event to happen. If it is not to
be the click, what do you envisage to do that? Do you want it to be when one
of I1:I100 changes?

Not sure what you mean by the second part, as the date does end up in
K1:K100, doesn't it?

--

HTH

RP
(remove nothere from the email address if mailing direct)