Timestamp a cell when row is updated.

I am looking for a solution.

My spreadsheet has 5 columns that i do not want this to affect. The 6th 
column is "Last Updated".

When i write comments in any cell after H i want it to put the updated date 
and time into "Last Updated" in that row.

Another addition to this would be to check the last updated date, if it is 
upto 3days from today then turn green, upto 5days yellow and more than 7days 
red!

I hope someone can help!
0
Utf
11/16/2009 12:01:01 PM
excel.programming 6508 articles. 2 followers. Follow

3 Replies
3326 Views

Similar Articles

[PageSpeed] 16

Hi,

generally this is very easy and the code is below but your question isn't 
clear.

Is the 'Last updated' column different for every tow?

Anyway try this. Right click your sheet tab, view code and paste the code 
below in

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.count > 1 Or IsEmpty(Target) Then Exit Sub
If Not Intersect(Target, Range("G1:z100")) Is Nothing Then
   Application.EnableEvents = False
        Cells(Target.Row, 6) = Now
    Application.EnableEvents = True
    End If
End Sub

Mike

"BakerInSpain" wrote:

> I am looking for a solution.
> 
> My spreadsheet has 5 columns that i do not want this to affect. The 6th 
> column is "Last Updated".
> 
> When i write comments in any cell after H i want it to put the updated date 
> and time into "Last Updated" in that row.
> 
> Another addition to this would be to check the last updated date, if it is 
> upto 3days from today then turn green, upto 5days yellow and more than 7days 
> red!
> 
> I hope someone can help!
1
Utf
11/16/2009 12:16:02 PM
The below will point you in the right direction. Select the sheet tab which 
you want to work with. Right click the sheet tab and click on 'View Code'. 
This will launch VBE. Paste the below code to the right blank portion. Get 
back to to workbook and try out.

--Events logged for changes after column H ie from col I onwards
--upto 3 days is colored as green, upto 7 days is colored as yellow and 
greater than 7 is coloured as red..You can change that to suit

Private Sub Worksheet_Activate()
lngLastRow = ActiveSheet.Cells(Rows.Count, "f").End(xlUp).Row
For lngrow = 2 To lngLastRow
If Range("F" & lngrow) <> "" Then
Select Case DateDiff("d", Range("F" & lngrow), Date)
Case Is <= 3
Range("F" & lngrow).Interior.ColorIndex = 10
Case Is <= 7
Range("F" & lngrow).Interior.ColorIndex = 6
Case Else
Range("F" & lngrow).Interior.ColorIndex = 3
End Select
End If
Next
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column > 8 Then
Range("f" & Target.Row) = Now
Range("f" & Target.Row).Interior.ColorIndex = 10
End If
End Sub


If this post helps click Yes
---------------
Jacob Skaria


"BakerInSpain" wrote:

> I am looking for a solution.
> 
> My spreadsheet has 5 columns that i do not want this to affect. The 6th 
> column is "Last Updated".
> 
> When i write comments in any cell after H i want it to put the updated date 
> and time into "Last Updated" in that row.
> 
> Another addition to this would be to check the last updated date, if it is 
> upto 3days from today then turn green, upto 5days yellow and more than 7days 
> red!
> 
> I hope someone can help!
0
Utf
11/16/2009 12:24:02 PM
Right click on sheet tab

Paste this code

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column >=3D 8 And Target.Column <=3D 12 Then
        CurR =3D Target.Row
        Range("F" & CurR).Value =3D Now
End If

End Sub

For Coloring use the conditional formatting

to color Green - use this
=3DTODAY()-INT(F2)<=3D3

to color Yellow - use this
=3DAND((TODAY()-INT(F2))>3,(TODAY()-INT(F2))<=3D5)

to color Red - use this
=3DTODAY()-INT(F2)>7

What about if the last updated in last 6 days ?


On Nov 16, 5:01=A0pm, BakerInSpain
<BakerInSp...@discussions.microsoft.com> wrote:
> I am looking for a solution.
>
> My spreadsheet has 5 columns that i do not want this to affect. The 6th
> column is "Last Updated".
>
> When i write comments in any cell after H i want it to put the updated da=
te
> and time into "Last Updated" in that row.
>
> Another addition to this would be to check the last updated date, if it i=
s
> upto 3days from today then turn green, upto 5days yellow and more than 7d=
ays
> red!
>
> I hope someone can help!

0
muddan
11/16/2009 12:36:54 PM
Reply:

Similar Artilces:

Office X 10.1.6 Update: Fonts missing?
> This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. --B_3182711903_8674241 Content-type: text/plain; charset="US-ASCII" Content-transfer-encoding: 7bit After updating to 10.1.6, some fonts are missing (KidPrint, Lucida handwriting, etc.) Any ideas? --B_3182711903_8674241 Content-type: text/html; charset="US-ASCII" Content-transfer-encoding: quoted-printable <HTML> <HEAD> <TITLE>Office X 10.1.6 Update: Fonts missing?</TITLE> </HEAD> <BODY> <FON...

Find Next Row With No Value In It
I've got this nextrow = Cells.Find(what:="*", searchdirection:=xlPrevious, searchorder:=xlByRows).Row + 1 Cells(nextrow, 1).Select which will find nextrow with nothing in it and select cell in Col A, but now I've got formulas in the cells, so instead of finding the next row with nothing in it, I want to find the next row with no value - even if it's got a formula in it. Can I do this by amending what's in the what:="*" part of this code? It's always better to include all the parms for .find(). If you don't, then you're at ...

Need VB code to edit cell values
I need some VB to do the following please (My VB is not good enough yet) Starting from the active cell (call it the StartCell) In the cell to the left.. ThreeChars = Value.right(3) (the last three chars) Value = Value - last three characters (assume value is text & at least 3 chars present) In StartCell.. value = ThreeChars (as text, overwrite any contents of StartCell) move active cell one down from StartCell Examples of before/after -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett@gmail.com "Stephen Ford" <Stephen_Ford_no@spam_uwclub....

Updating PO Line Qtys should not remove existing AA with 100% Assi
Assuming AA is setup and you have at least 1 Dimension linked to an Account, and that Dimension has a Dimension Code attached. 1. Go into PO Entry 2. Enter a line item 3. Go into Purchasing Item Detail entry and make sure your Inventory Account is an account linked to your AA Class and therefore Dimension. 4. Enter AA information against this account on the PO - using the default of 100% Assignment. 5. Return back to the PO Entry window. 6. Change the Qty on the PO Line to be 1 greater (or less). Press tab to move off the field. Actual Result: The already assigned AA data for that line...

Search for a text in a cell and give the outcome later
Dear expert, C30 to C60 contains some data. Say I wan to find piano and use =MATCH("*piano*",C30:C60,0) in cell D31 It gives me the anwer 20 in D31. 20 is the squence of piano. How can I use the function and use return the the whole cell content which is "Play Piano"? I only know where it is. How to locate where and give the whole content in D32 please? Thanks Elton Play games Play golf Play guitar Play piano Play Kite Play Sccoer Play cards Play WII Play music =INDEX(C30:C60,D31) -- Regards! Stefi „Elton Law” ezt írta: &g...

Changing a cell color....
Hi, Can someone help me how can I change a cell color using the"IF" command??? Thank you, Shaul On Mon, 27 Oct 2003 22:27:56 +0200, Shaul <bagaz@netvision.net.il> wrote= : > Hi, > Can someone help me how can I change a cell color using the"IF" = > command??? Under the Format menu, pick Conditional Formatting. -- = Dave dvt at psu dot edu ...

How can I put a text box in every cell?
Two questions: -How can I put a text box in every cell that I am using WITHOUT having to insert on manually every time? AND - How can I make sure that a text box inserts itself automatically into a new row whenever I add a row? Thanks! One way is to select the row with the text box and copy it to the next row. Copy and paste it several times and then select the rows and paste them as a group. "bmowell" wrote: > Two questions: > > -How can I put a text box in every cell that I am using WITHOUT having to > insert on manually every time? &g...

record sheet row order, then restore to that point
There is the ability to undo a sort so I suspect the information I want is somewhere. Is there a way to take a "snap shot" of a sheet's current row order, and then restore that row order at any point in the future? During sheet processing, rows will not be added, but data in any cell may change. The only other way I can think of doing this, is to record the order of some "signature" column and then use it to re-write the processed sheet to a scratch sheet. Thank you Add a new column before you do the 1st sort with number 1 to last row. then sor tby ...

Simple update Query
Hi, I'm sure I've done this in the past but can't figure how. I have 2 tables "A" and "B" both with the following fields Town; Council I want to update Table "A"'s Council field with the Council information in table "B" Could someone please give me the SQL code to do this. Thanks in advance UPDATE TableA INNER JOIN TableB ON TableA.Town = TableB.Town SET TableA.Council = TableB.Council. IF you can only do this using design view -- Open a new query -- Select the two tables -- Set up a join by dragging from town to town -- add the Cou...

how can I split one cell's data into two cells?
I am using Excel as a database, and have a column with cells with a person's name (text) in it...e.g., John Smith. NOW, after the fact, I wish to have a column with just John in it, and another column with just Smith...but I have NO idea how to do this! Help would be appreciated. Thank you. Please refrain from multiposting, you have answers in one of the 4! newsgroups you posted the same question in. Multiposting is frowned upon and even the MS web interface states so -- Regards, Peo Sjoblom Excel 95 - Excel 2007 Northwest Excel Solutions www.nwexcelsolutions.com "It ...

How do you convert a formula cell to a constant cell
I use a formula to calculate the value, such as time difference, and want to save the value not the formula. The value (constant) will be used later to adjust both time cells used as arguments in the original formula--thus the need to preserve the value computed rather than the formula. After calculation, copy the cell with the formula then Edit - Paste Special - Values on the same cell. HTH "JQLogan" wrote: > I use a formula to calculate the value, such as time difference, and want to > save the value not the formula. The value (constant) will be used later to > ...

Windows XP updates not successful
I am continually getting told by XP (Service Pack 3) that updates are ready for my computer. When I try and install them, I get this: "The following updates were not installed: KB971486, KB969059, KB958869." I went through my Windows Update history, and the first update that was unsuccessful was KB971486. So I downloaded this update to try and install it manually (with the "WindowsXP-KB931784-x86-ENU.exe" /o command), and I got a message saying that "Setup has detected that the Service Pack version of this system is newer than the update you are applying...

average on rows
can we use average in rows even two rows at the time when number are not in consequent collums meaning there is some spaces between them without having to add each number manually in the function window. -- XP home , Office 97 any or all responses are gratefully accepted and thank you for your time Have you tried? Your description is confusing to me, but AVERAGE() ignores blank cells and text... In article <C2D622EE-D839-4469-9DA9-F264B074BB6B@microsoft.com>, justamailman <justamailman@discussions.microsoft.com> wrote: > can we use average in rows even two rows at the...

Create/Update Excel Objects in Power Point
I developed an add-in in Power Point 97, 2000, XP and 2003 for creating Excel charts/tables and being able to update them. The code I used is below: Dim PPT_CHRT As OLEFormat Dim PPT_SLD As Slide 'ADD a chart/table PPT_SLD.Shapes.AddOLEObject Left:=var_chrt_L, Top:=var_chrt_T, Width:=var_chrt_W, Height:=var_chrt_H, ClassName:="Excel.Chart.8", Link:=msoFalse Set PPT_CHRT = PPT_SLD.Shapes(var_SHP_count_no).OLEFormat PPT_SLD.Shapes(var_SHP_count_no).Select 'EDIT chart/table Set PPT_SLD = ActivePresentation.Slides(var_SHP_SLD_no) Set PPT_CHRT...

Never ending Updating your accounts
Hi, I upgraded my money to Plus Home and Business (v17). Since then I can not use the software because it always "updating your accounts" I already done couple repairs of the money file, removed all items from home page. I do not really want to delete all my bills. Is there anything I can do else to stop this. This is some kind of bug that money folks should repair ASAP. Are you getting a message that money is updating accounts? I am having a problem when inputing a deposit or payment scheduled for about a month in advance. When the entry is made money freezes and I have ...

Can databases be merged to update data?
Recently, two employees worked on the same database at two different locations on two different workstations. After we entered data into the databases, we cannot seem to merge our information back into one database. Atleast the help files didn't provide a solution here. Can anyone direct us to the appropriate newsgroup if this isn't the site for the answer we are seeking? If you can answer our question and provide us with the information to merge our databases into one of the databases, please help. Thank you in advance. Hi Mark, Working with copies of your databases, u...

Problem with empty cells when creating chart using vba
Hello, I am facing difficulties to create a chart from a sheet containing empty cells (I use Excel 2003 SP2). My sheet looks similar to this: A B C 1 0.1 2 0.2 25 34 3 0.3 23 27 4 0.4 24 6 5 0.5 25 16 6 0.6 27 21 7 0.7 Column A contains data (some depth in my case). Each cell in column A contains data. Column B and C contain some measurements made at the depth indicated in column A. I need to combine two graphics on one chart: the content of B in function of A and the content of C in function of A. The ...

Paste in a right cell
How do I set that I ONLY can paste a col A info from sheet1 into sheet2 at cells C3 and S3? I'd like to have an error (pop up) if I paste in wrong cell. ONLY allow to paste in cell C3 and S3. Thanks What version of Excel? In 2002 and 2003 you could lock all cells except C3 and S3. Then Protect the sheet with "Select locked cells" unchecked so only the unlocked C3 and S3 can be selected. Gord Dibben Excel MVP On Wed, 28 Sep 2005 10:01:35 -0700, bioyyy <bioyyy@discussions.microsoft.com> wrote: >How do I set that I ONLY can paste a col A info from sheet1 into sheet...

how to sum cells and ignore the #div/0! 's ?
I an working with the following formula: =SUM(C8:E8)/COUNTIF(C8:E8,">0") if I initially have no data entered i get the #div/0! 's is there a fix for this? =IF(COUNTIF(C8:E8,">0")=0,"",SUM(C8:E8)/COUNTIF(C8:E8,">0")) -- David Biddulph "Bryan" <Bryan@discussions.microsoft.com> wrote in message news:62E28129-5C01-44B7-84EC-2851E94569B1@microsoft.com... > I an working with the following formula: =SUM(C8:E8)/COUNTIF(C8:E8,">0") > if I > initially have no data entered i get the #div/0! 's ...

Cell formatting or formulas
How do I make 1.50 appear as 1.30 in a given cell? I have a cell with times such as 1.00 minute, 1.50 minutes, 2.50 minutes, 3.00 minutes, etc. I want all the x .50 digits to appear as x.30 digits (30 seconds to a half minute) Divide by 1440 and format as mm:ss -- Regards, Peo Sjoblom (No private emails please, for everyone's benefit keep the discussion in the newsgroup/forum) "George" <George@discussions.microsoft.com> wrote in message news:0E77414D-FED2-4BB7-BA36-E226D3AE4D05@microsoft.com... > How do I make 1.50 appear as 1.30 in a given cell? I have a c...

How do I insert a row (space) between bulleted (#) paragraphs?
I would like to insert a row or space between numbered paragraphs. When I press Enter 2 times then a I get several blank rows (spaces) between the paragraphs and when I try to delete the extra rows to get just one row between the paragraphs, it automatically goes back to where it was before, no row or space between the numbered paragraphs. I would appreciate your help in figuring this out. Thank you. Apply some Spacing After (or Spacing Before) to the bulleted paragraphs. You'll find that setting in the Paragraph dialog box. To open the dialog box: - Click Format | Para...

Add rows on worksheet by date?
I've been asked to create a spreadsheet that will track vehicle miles by route. The drivers will note the odometer when they start the route and the odometer when they return, so we can log that across a row: Vehicle - Route - Odo Start - Odo End - Calc Route Miles They also want miles by vehicel by day. Since a vehicle can travel more than one route in a day, is there an easy way to do this? My original thought was to have input through validation drop-downs to choose teh vehicle and the route - now I'm thinking of using a Form, and the Enter Data button will automatically sort by...

Updating different worbooks to workbook
Is there a simple way update certain parts of a workbook from other workbooks? It depends. It may be that you could use a custom macro or simply copy and paste. The best way would be determined by the specifics of what the differences are and what changes need to be made, and what you mean by update - combine? write-over? change the formulas to match? HTH, Bernie MS Excel MVP "aggi" <anonymous@discussions.microsoft.com> wrote in message news:2cd6f01c46a77$05e6c9c0$a601280a@phx.gbl... > Is there a simple way update certain parts of a workbook > from other workbooks? ...

Plugin.ocx removed with MS Updates
Plugin.ocx file continues to be removed when we run MS Updates. The databases are 2003 and forms don't work without the plugin.ocx. Tried making the .ocx file read only, but that didn't work. We have to copy it back into Windows System folder each time we run updates. Any ideas? -- aleon ...

copy graphs values in a cell
I am looking for a way to click on a specific data point of a graph and have the data related to that, copied in one or more cells..like click on point x1,y1 belonging to Graph1, and have the values of x1 and y1 copied in cells A1 and A2 in Sheet1 I guess I need a macro... You think it might be possible? ps sorry for my poor english Hi, Yes you will need VBA code. Have a look at Jon Peltier's article on chart events. http://www.computorcompanion.com/LPMArticle.asp?ID=221 Cheers Andy bluepill wrote: > I am looking for a way to click on a specific data point of a graph and have &g...