Automatically adding today's date, but not updating when ope

Rainbow

Board Regular
Joined
Oct 3, 2003
Messages
65
Hi there.
I'm not sure if this can be done, but I thought I would ask.

I would like cell in D2 to add todays date when any text is entered into Colum A2 and not have that date update the next time I open the worksheet.

When I use =TODAY() the cell will update when ever I open the workbook.

Also I would like F2 to calculate 6 weeks from a date that I enter into E2 and change the entire row Yellow when that date is reached.
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Re: Automatically adding today's date, but not updating when

Hi Steve:
....
I'm not sure if this can be done, but I thought I would ask.

I would like cell in D2 to add todays date when any text is entered into Colum A2 and not have that date update the next time I open the worksheet.

When I use =TODAY() the cell will update when ever I open the workbook.
....
In response to the first part of your question, I suggest you try the following VBA solution ...
Code:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Target.Column <> 1 Then Exit Sub
    If Not (IsNumeric(Target.Value)) Then Range("D2") = Date
End Sub
 
Upvote 0
Then you need a vba code like
to the sheet module
Code:
Private OldData

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
OldData = Target.Value
End Sub

Private Sub Woorksheet_Change(ByVal Target As Range)
With Target
   If .Address(0,0) <> "A2" Then Exit Sub
   If .Value <> OdlData Then
      Application.EnableEvents = False
      Range("D2").Value = Now()
      Application.EnableEvents = True
   End If
End With
End Sub
 
Upvote 0
Re: Automatically adding today's date, but not updating when

....
Also I would like F2 to calculate 6 weeks from a date that I enter into E2 and change the entire row Yellow when that date is reached.

In response to second part of your question, you can use Conditional Formatting ...

1) Select entire row 2
2) invoke Conditional Formatting
3) use formula is ... =TODAY()>=DATE(YEAR($E$2),MONTH($E$2)+6,DAY($E$2))
Pattern Yellow

I hope this helps!
 
Upvote 0
Thanks for the quick responses.

It's been a long time since I've had to work with Excel and I've forgotten most of what I knew.

Where do I put the VBA Codes listed above?

I know it's Tools>Macros>Vsual Basic Editor, but what from there?
Is it a module or attached to the worksheeet?

Do I need to use both of them?
 
Upvote 0
From Excel;

1) right click on the sheet tab then [ViewCode]
2) paste the code one of them
3) click x to close the window to get back to Excel

-1 Yogi's will work when anything other than number entered in columnA
-2 Mine only works when A2 changed from previos entered value in A2
 
Upvote 0
Re: Automatically adding today's date, but not updating when

....

1) Select entire row 2
2) invoke Conditional Formatting
3) use formula is ... =TODAY()>=DATE(YEAR($E$2),MONTH($E$2)+6,DAY($E$2))
Pattern Yellow

That worked great for that part of it, but I would like the row to remain clear or white if nothing is entered into E2.
 
Upvote 0
From Excel;

1) right click on the sheet tab then [ViewCode]
2) paste the code one of them
3) click x to close the window to get back to Excel

-1 Yogi's will work when anything other than number entered in columnA
-2 Mine only works when A2 changed from previos entered value in A2

Sorry Jindon;

I tried both of them just like you said and I get nothing.
Also I would like this to work for any row not just row 2,
If I enter something in A3 or A15 it will enter the date in D3 or D15 respectively.
 
Upvote 0
Make sure that you paste the code onto sheet module inquestion.
Code:
Private OldData

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
OldData = Target.Value
End Sub

Private Sub Woorksheet_Change(ByVal Target As Range)
With Target
   If .Column <> 1 Then Exit Sub
   If .Value <> OdlData Then
      Application.EnableEvents = False
      .Offset(,3).Value = Now()
      Application.EnableEvents = True
   End If
End With
End Sub
 
Upvote 0
I still got nothing.

I right clicked on my "Porject Partitions" tab.

Clicked on "View Code"

The VB Editor opened up with my Sheet 1 (Project Partition) Highlighted.

I pasted the code in the screen to the right that was open.

Excel.jpg


Then closed the VB Editor.

I also tried inserting a module from Insert>Module and pasting the code into that and I still got nothing in Colum D.

I've put VB Code in in the past and it has worked, but it has been over 2
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,025
Members
448,543
Latest member
MartinLarkin

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top