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.
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
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
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
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
 

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
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!
 

Rainbow

Board Regular
Joined
Oct 3, 2003
Messages
65

ADVERTISEMENT

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?
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
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
 

Rainbow

Board Regular
Joined
Oct 3, 2003
Messages
65

ADVERTISEMENT

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.
 

Rainbow

Board Regular
Joined
Oct 3, 2003
Messages
65
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.
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
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
 

Rainbow

Board Regular
Joined
Oct 3, 2003
Messages
65
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.



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
 

Watch MrExcel Video

Forum statistics

Threads
1,113,946
Messages
5,545,133
Members
410,662
Latest member
luke_101
Top