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
3182 Views

Similar Articles

[PageSpeed] 11

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:

EXCEL2002: Insert row with all formulas from row above
Hello, is there any way to insert a row with all formulas from the row above? Thanks and greetings Udo See David McRitchie'w InsrtRow: http://www.mvps.org/dmcritchie/excel/insrtrow.htm In article <6e872a06-4b1a-4a0e-af5e-16277744d1c5@e23g2000prf.googlegroups.com>, Udo <WeikEngOff@aol.com> wrote: > Hello, > > is there any way to insert a row with all formulas from the row above? > > > Thanks and greetings > Udo ...

Base a cell value on another cell's value
I want to base a cell value on another cell's value. In other words, if cell A2 <0, then in B2 I want to put the word "attrition". If cell A2 >0, then I want to put the word "hire". Thanks in advance. in b2 =if(a2>0,"hire","attrition") -- Don Guillett SalesAid Software donaldb@281.com "jh" <anonymous@discussions.microsoft.com> wrote in message news:612b01c4821a$e7ced930$a501280a@phx.gbl... > I want to base a cell value on another cell's value. In > other words, if cell A2 <0, then in B2 I want to put th...

compare cell data
I am looking for a formula/function that will compare the data in each cell in Col A, for example, with each cell in Col B, AND vice versa. There would ideally be some output to Col C that would alert me that there is an entry in Col B that is not in Col A, and vice versa. (It may be that the Col A to Col B "compare" will be output to Col C; and the Col B to Col A "compare" with be output to Col D.) Or there may be some other manor of "notification," such as colors in conditional formatting. Word has this feature in its "Compare and Merge Documents" f...

Before Update event for Form
Why is it I cant use 'setfocus' in this event? I validate field contents here and would like to set the focus to fields that are invalid. Am I doing this wrong? Is there a better way? Thanks. mscertified wrote: >Why is it I cant use 'setfocus' in this event? I validate field contents here >and would like to set the focus to fields that are invalid. Can't tell what's wrong without seeing the event procedure, but what you are describing is the normal way to do it. -- Marsh MVP [MS Access] ...

restricting entry into a cell based on entry to a previous cell
I have a group of cells for an inventory sheet. I want to prevent double or incorrect entry. For example, if there is any entry into cell F6 they cannot place an entry into F7 or F8. The same would apply for entry into F7 which would restrict entry to F8 and F6. Can this be done? Use Data Validation Data > Validation... > Custom > Formula is: =COUNTA($F$6:$F$8)=1 apply this to F6, F7, F8 -- Gary''s Student - gsnu200790 "newbie57" wrote: > I have a group of cells for an inventory sheet. I want to prevent double or > incorrect entry. For example, if t...

Windows update has failed in exchange 2003 Front-end server
Hello, I have been trying windows updates on exchange server but has failed with error message below: Updates were unable to be successfully installed The following update wre not installed: Security Update for Windows Server 2003 (KB917734) Security Update for Windows Server 2003 (KB914389) Security Update for Windows Server 2003 (KB917344) Security Update for Windows Server 2003 (KB918439) Security Update for Windows Server 2003 (KB911280) Cumulative Security Update for Internet Explorer for Windows Server 2003 (KB916281) Do you guy how to resolve those problems? Thank you There s...

calculation of cells
Periodically I open a work book and the calculation option has been changed to manual and I cannot figure out why. It seems that it would have to be done by a user and most of my spreadsheets are only used by me. Any ideas out there Mark, Calculation, auto or manual, is set by the first workbook that's opened. It is that way for any other workbooks opened in that instance of excel. Look for a workbook you might have opened first that's been set to Manual and saved that way. Go figure. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------...

Retriving column header for non empty cells in a row
Hello, I have worksheet like one below No Att1 Att2 Att3 Att4 Att5 A 1 1 1 B 1 1 1 1 C 1 1 D 1 1 E 1 1 1 F 1 1 1 1 I want to know a formula that will return column header for second non empty cell in a row. fo ex. for A formula should return Att3 Also if you can tell about how to modify formula so as that it will return column header for nth non empty cell in row. Thanks and Regards SushiL ...

Insert row
Hi Can anyone help with this problem? I have a worksheet of 500 names. Columns A-P contain user inserted data. Column Q contains the formula (typical) =IF(COUNTIF(E5:P5,"")<12,"1","") Column R contains (typical) =B5 When I insert a row, the formula in Column R is copied OK. The formula in column Q is not copied. Can anyone say why this is happening? Thanks georgeporge ...

Find formatted cells
Hi Is there a way in VBA to scan a range of cells and pick up the references of any cells that have a particular format applied? The range may have more than one cell with the format applied and I want to be able to use the references elsewhere in the workbook. Many thanks. Jim xl2002 added the ability to find by format. Before that you could have a macro that would search through your sheets looking for cells that match a specific format. But there are lots of things that are in the format of the cell. If you limit your search criteria (font/boldness/fill color/font color), you may eve...

excel 2003 no of cells
Hi ! DOes anyone know if the size of a worksheet in excel has increased in 2003 version compared to 2002. We have some reports that are bigger than excel but cannot find any info re the size of the new version. Hope someone can help ! Thanks in advance Carolyn Same size - 65536 rows and 256 columns. -- Jim Rech Excel MVP "Carolyn Bromley" <anon@anon> wrote in message news:uJzl9tMREHA.3300@tk2msftngp13.phx.gbl... > Hi ! > > DOes anyone know if the size of a worksheet in excel has increased in 2003 > version compared to 2002. > > We have some reports ...

Updating Supplier Info
I have created about 4000 new items but left out the Supplier information in the Supplier tab. All the new items have the same supplier name. I have all the Item Lookup Codes. Is there an easy way to use excel or csv file to upload the Supplier info according to the Item Lookup Codes? Thanks You have a couple of options... 1. Export all your item information into an excel file add the Supplier's Name then using the QSImport tool and reimport the items with 'Treat duplicate records as errors' turned off. This will add the Supplier to of those items. But you will need all the ...

Updating workflow
Hello, If I need to update an existing workflow rule (to add extra data slugs to an email for instance) will the rules that are already running in the queue but have not yet finished be updated with the changes? thank you Jason On Jun 2, 1:02=A0pm, Jason <Ja...@discussions.microsoft.com> wrote: > Hello, > If I need to update an existing workflow rule (to add extra data slugs to= an > email for instance) will the rules that are already running in the queue = but > have not yet finished be updated with the changes? > > thank you > Jason No, the WFs that are alr...

Metadata and Timestamps
Since we were just talking about this subject, those who were following the discussion might be interested in the Explainer on Slate.com today. It is about faking timestamps on digital photos by tweaking the metadata, and has some comments on usage in the legal system, authentication, chain of custody, and so forth. Susan ...

Merge Cells not available
I am attempting to merge cells in a spreadsheet, but the Option to merge both on the toolbar and in 'Format'-'Cell' are greyed out. My worksheet is not protected. Every Worksheet in the document acts the same. If the workbook is shared (Tools, Share Workbook) merging cells is not allowed. -- Jim Rech Excel MVP "Flash24" <Flash24@discussions.microsoft.com> wrote in message news:BB8D4738-3CD6-4575-B44E-02FA32D88B9B@microsoft.com... |I am attempting to merge cells in a spreadsheet, but the Option to merge both | on the toolbar and in 'Format'-'...

Wndws XPsp3: Auto Update ON but system nags all day to Install Upd
I have my Auto Update set for AUTOMATIC to download/install at 3 am daily BUT I get at least 4-5 messages per day that "Updates have been found formy computer". Constantly updating/restarting. This is driving me CRAZY. Please help. ME wrote: > I have my Auto Update set for AUTOMATIC to download/install at 3 am > daily BUT I get at least 4-5 messages per day that "Updates have > been found formy computer". Constantly updating/restarting. This is > driving me CRAZY. Please help. We now know the following blanks in the puzzle... Windows XP ______ ...

Named cells
How do I use a name of a cell rather than the R1C1 address. I have tried Range("Name") = Range(Name) = Thanks Dave, What happened when you tried Range("Name") = -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Dave Griffiths" <dave@daveg.co.uk.nospam> wrote in message news:4oOUb.2185$rj4.31303@news2.e.nsc.no... > How do I use a name of a cell rather than the R1C1 address. > > I have tried > Range("Name") = > Range(Name) = > > Th...

Doubleclik on border of cell
Hello, When I doubleclick on the bottom border of a cell in Excel, the selected cell changes to last cell of the column. Is it possible to turn of this feature? Greetings, Marc Hi I don't think it's possible to turn this 'feature' off! It also does it double-clicking on the other borders - so watch out! -- Andy. "Marc Gritter" <m.gritter@awl.nl> wrote in message news:Of8YH$KaEHA.2632@TK2MSFTNGP10.phx.gbl... > Hello, > > When I doubleclick on the bottom border of a cell in Excel, the selected > cell changes to last cell of the column. Is it...

Manipulating Cell Contents?
Can anyone help me with this problem? I've imported data from a .pdf file into Excel. But some of the numbers don't end up in the correct columns. CARVER PRIMARY $3,547,3 03 $5,6 CASALS $4,406,4 52 $5,7 CASSELL $2,047,2 25 $6,7 CASTELLANOS $3,989,1 48 $5,0 CATHER $2,555,8 51 $6,4 In the third column, above, the two-digit numbers actually need to be appended to the end of the numbers in column two. Is there a way I can cut these numbers and move them over to the second column. I suppose I could just type them in, but there are...

Delete rows from a table
What I'd like to do is delete rows from a table if a specific cell does not equal a certain value. I'm don't even know where to start, so several helpful hints would be appriciated. Sub ScratchMaco() Dim oTbl As Word.Table Dim oRow As Word.Row Dim oCell As Word.Cell Dim oRng As Word.Range Set oTbl = Selection.Tables(1) 'The table the IP is located in For Each oRow In oTbl.Rows Set oCell = oRow.Cells(2) 'The specific cell (e.g., second cell in row) Set oRng = oCell.Range oRng.End = oRng.End - 1 'Strip end of cell marker If oRng.Text <> &...

Add a number to the current cell
I have a cell that contains a value, eg 10. If I then type 5 whilst thi cell is selected, 10 is replaced by 5. However, what I would like i for the 5 to be added to the current value, 10, so that the new cel value is 15, rather than 5. Can you help? Thanks, Kir -- Message posted from http://www.ExcelForum.com Hi i can help you with a trick if your incremental increase is constan of a certain number you want . first from your toolbar view allow the forms and then click the spinne and insert in a place then click right and open format control , unde incremental change , write the number you ...

Hiding & Unhiding Cells, How to
XL 2003 & 97 This should be easy but .... Attempted to hide certain cells by alternately clicking or unclicking (Locked / Hidden) boxes on the respective Cells' Protection Tab - then locking the worksheet. My luck ...!? the cells are still visable! What obvious step am I missing? ********************************** The real goal is that I want (via VBA) to unhide all hidden cells in all worksheets in a workbook. I have the code to unhide Rows and Columns. (What about individual cells? Should that be a separate step? I think so.) In addition, I would like to find, and reset...

Update KB 977165 is it safe yet?
I have had updates turned off since the news of the buggy BSOD causing update on Feb 9. Has MS fixed this yet? regards -- Craig News wrote: > I have had updates turned off since the news of the buggy BSOD causing > update on Feb 9. Has MS fixed this yet? The 'problem' - AFAIK anyway - turned out to be infected/infested machines. If your machine is not infested/infected, in particular with: Win32/Alureon.A http://www.microsoft.com/security/portal/Threat/Encyclopedia/Entry.aspx?Name=Virus%3AWin32%2FAlureon.A More information on the ongoing i...

Higher value in cell b Than in Cell A
The user inputs numeric data into cells c7 to j7. The values must b higher than the value in cells c6 to j6. IE: The data is already present in c6 to J6. C6 must be a higher value than C7 D6 must be a higher value than D7, etc, etc is there a way to make this work -- Message posted from http://www.ExcelForum.com You could try select C7:J7 with C7 as the active cell, do data>validation>allow custom and in the formula box put =(C7>C6)*(ISNUMBER(C7)) -- Regards, Peo Sjoblom "mikewild2000 >" <<mikewild2000.10xfio@excelforum-nospam.com> wrote in messag...

Find MAX data in sheet (Cell)
How would one go about finding the cell that contains the MAX info, o say any/all cells that exceeds 8,000 characters/spaces in a Excel page -- confuzedagai ----------------------------------------------------------------------- confuzedagain's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2943 View this thread: http://www.excelforum.com/showthread.php?threadid=49148 1. Highlight the entire document 2. Select Format->Conditional Formatting 3. Select "Formula Is" 4. Input this formula =LEN(A1)>8000 5. Format with something obvious like green bac...