That worked perfect. One more question.

The purpose of the spreadsheet is to scan a barcode and get the exact date &
time the barcode was scanned. I think I am almost there with your help. I
have a separate worksheet I scan all these barcodes into. Then I have
formulas that break down the values in the barcode and put them into separate
cells. right now the macro is working off of a range of cells that require
human data entry. Is there a way to have the macro work off of lets say colum
"M"? This colum is populated once the barcode is scanned. Here is the current
formula in colum "M".

=RIGHT(LEFT($A2,36),8)

I guess what I am trying to get to is, once the barcode is scanned, I would
like to have the date and time appear automatically. Right now it requires
human data entry into one cell. Thanks for your help.

"Gary''s Student" wrote:

You don't need any formulas. Leave the col D & C area completely empty.
Discard the previous version. Here is the new version:

Private Sub Worksheet_Change(ByVal Target As Range)
Set t = Target
Set r = Range("B2:B10")
If Intersect(r, t) Is Nothing Then Exit Sub
If t.Offset(0, 1).Value < "" Then Exit Sub
Application.EnableEvents = False
t.Offset(0, 1).Value = Date
t.Offset(0, 2).Value = Time
Application.EnableEvents = True
End Sub

You should adjust the "B2:B10" to suit your needs.
--
Gary''s Student - gsnu200818


"BAKERSMAN" wrote:

Here is my exact scenario. I need the Date to populate in colum C and I need
the time to populate in column D. I have coppied the macro into the
worksheet. Do I need to keep my formula in the cells that I want the static
data to stay in?

"Gary''s Student" wrote:

Say we are entering data in B2 thru B10 and want the date/time recorded in C2
thru C10.

But we want the date/time to be static.

Enter the following event macro in the worksheet code area:

Private Sub Worksheet_Change(ByVal Target As Range)
Set t = Target
Set r = Range("B2:B10")
If Intersect(r, t) Is Nothing Then Exit Sub
If t.Offset(0, 1).Value < "" Then Exit Sub
Application.EnableEvents = False
t.Offset(0, 1).Value = Now
Application.EnableEvents = True
End Sub


Because it is worksheet code, it is very easy to install and use:

1. right-click the tab name near the bottom of the window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.

To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm


--
Gary''s Student - gsnu200818


"BAKERSMAN" wrote:

I need to have the time and date auto populated in 2 different cells when a
value is enterd into a different cell. Here is the formula i have now

=IF(LEN(B2)=1,NOW(),"")

This formula works perfect but here is the problem I run into. Lets say cell
B2 equals "1", the date will populate in the cell the formula is entered
into. Lets says I enter "1" into cell B3 the next day, the cell with the
formula will populate the current date but the above cell's date will change
to the current date also. Is there a way to keep the date and times from
updating to the current time for all cells with this formula?