Worksheet Change Event-change the range in my VBA

Hi all,

I have a worksheet change event for when a value is entered into cell B2 as follows -

If Range("B2") <> "" Then
Range("A2").Value = Now()

This successfully inputs the "NOW" value (I want the value so it does not update - so an IF worksheet function cannot be used) into A2.

I would like for this to work anywhere on the column range...so if B3 has a value, A3 displays now...and so on.

I have tried to change the range in my VBA - and have used

Range(B, B)
Range("B:B")
Range(B2, B3000)
Range("B2:B3000")

But nothing seems to work correctly. The closest I have got was to get the whole A column to populate with the value of now.

Any ideas on what I am doing wrong?

Thanks

Rob

Try

If Not Intersect(Target,Columns("B:B")) Is Nothing Then
IF Target.Value <>"" Then
Target.Offset(0,-1).Value = Now
End If
End If

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"systemx" <[email protected]> wrote in
message news:[email protected]...
>
> Hi all,
>
> I have a worksheet change event for when a value is entered into cell
> B2 as follows -
>
> If Range("B2") <> "" Then
> Range("A2").Value = Now()
>
> This successfully inputs the "NOW" value (I want the value so it does
> not update - so an IF worksheet function cannot be used) into A2.
>
> I would like for this to work anywhere on the column range...so if B3
> has a value, A3 displays now...and so on.
>
> I have tried to change the range in my VBA - and have used
>
> Range(B, B)
> Range("B:B")
> Range(B2, B3000)
> Range("B2:B3000")
>
> But nothing seems to work correctly. The closest I have got was to get
> the whole A column to populate with the value of now.
>
> Any ideas on what I am doing wrong?
>
> Thanks
>
> Rob
>
>
> --
> systemx
> ------------------------------------------------------------------------
> systemx's Profile:
http://www.excelforum.com/member.php...o&userid=29254
> View this thread: http://www.excelforum.com/showthread...hreadid=505652
>