# Automattic Date in cell below

#### Boo204

##### New Member
Hey all Frist time poster... But I have found the info of the board useful in more then once.

Here is my question:

I am tring to setup a sheet the when texted is entered into one cell then a static date is automattly ented in the cell below.

Below is the formula that I found on the borad and tried to modifty. but when i do it runs a cycle and repets the answer all down the column

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
Intcolumn = Target.Column
introw = Target.Row
Cells(introw, Intcolumn + 1) = Now()

End If

End Sub

Anyone have any Ideas????

Thanks

Matthew

### Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Hi Matthew, welcome to the board! Err, posting!

If you right click your sheet tab and click View Code, paste this in there, it may help ...

<font face=Tahoma New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN>

<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)
<SPAN style="color:#00007F">If</SPAN> Target.Column = 1 <SPAN style="color:#00007F">Then</SPAN>
Target.Offset(, 1).ClearContents
<SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> IsEmpty(Target) <SPAN style="color:#00007F">Then</SPAN> Target.Offset(, 1) = Format(Date, "dd-mmm-yy")
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

.. change the formatting of the date as desired.

HTH

Thanks for the input but that not quite what I'm looking for.

Below id the code that you gave me ( Thanks a bunch) Changed to do what i'm trying to do

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
Target.Offset(, 1).ClearContents
If Not IsEmpty(Target) Then Target.Offset(1, 0) = Format(Date, "dd-mmm-yy")
End If
End Sub

the problem is when it insert the date into the cell it repeats down the column. If you have any other idea I would love to here them

Matthew

Ok, sorry. You need to disable events as it will loop on itself if you don't. (I always forget that one until last.) Maybe you can use ...

<font face=Tahoma New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN>

<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)
<SPAN style="color:#00007F">If</SPAN> Target.Column = 1 <SPAN style="color:#00007F">Then</SPAN>
<SPAN style="color:#00007F">If</SPAN> Target.Cells.Count > 1 <SPAN style="color:#00007F">Then</SPAN>
MsgBox "This does not work with more than one cell" & vbNewLine & _
"selected at a time." & vbNewLine & vbNewLine & _
<SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN>
Target.Offset(, 1).ClearContents
<SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> IsEmpty(Target) <SPAN style="color:#00007F">Then</SPAN> Target.Offset(1, 0) = Format(Date, "dd-mmm-yy")
Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

Right now that did it Thanks a BUNCH!!!!!

Matthew

Replies
3
Views
283
Replies
5
Views
433
Replies
4
Views
326
Replies
1
Views
208
Replies
4
Views
191

1,203,244
Messages
6,054,366
Members
444,719
Latest member
saathvik

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back