Time to show when data was entered.

[markjohnson002]

Hello all,
I'm thinking this must be really simple, but is there a way i can get the time and date to show when data is added into a cell.



E.G - If i enter a 'case' number into Cell A1, could i get the date and time to automatically go into cell B1.

Following on from this, if in cell C1 i had severity level (e.g Critical) could i then get the row to turn flashing red if there was no resolution in cell D1 with a certain time limit?

Everyones help is very much appreciated!

 

[VoG]

For the first part, right click the sheet tab, select View Code and paste in:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
    Application.EnableEvents = False
    Target.Offset(0, 1).Value = Now
    Application.EnableEvents = True
End If
End Sub

For the second part, flashing cells cannot be achieved with Excel native functions (though it can be done). How about colouring the entire row using Conditional Formatting - see http://www.contextures.com/xlCondFormat02.html

 

[markjohnson002]

Ok the code you gave me is excellent. Thankyou very much.
I'd be very happy to just have a red row instead of a flashing one.

I'll just explain the issue a little further.

Excel Workbook
	A	B	C	D	E	F	G
1	Case no.	Date Created	Date Closed	Created by	Priority	Client	Detail
2	9502	10-Oct-08	*	Joe Blogs	Critical	Sony	Client needs access to system
Sheet1

I would like Column E to act as a deadline, so if the case isn't closed by a certain time limit the row will turn red.
Time limits
Critical - 4 hours
High - 24 hours
Medium - 72 hours
Low - 120 hours

How would i incorporate these deadlines so that using the time stamp in column B, the rows will either turn red if deadline missed, or stay clear if deadline has not yet passed?

thanks again for your time and help

 

[VoG]

This would be easier if in column E you had numerical values e.g. 4 instead of Critical. You could then use the following CF formula

=(NOW()-$B1)*24>$E1

to colour the entire row if the deadline had expired.

 

[markjohnson002]

Ok, i went along the same lines (roughly) changing my Critical/High/Medium/Low to C/H/M/L and ended up using - =(NOW()-$B6)*24>(IF($D6="C",4,IF($D6="H",24,IF($D6="M",72,IF($D6="L",120,"ON HOLD"))))) - with conditional formatting, so it highlighted the cell red if i missed a deadline.

Excel Workbook
	A	B	C	D	E	F	G	H
1	KEY	C	Critical	4	Description of field and what information is required
2		H	High	24	Who created the case?	Which client does the case effectE.g Lenovo or Global	Brief description of case	Is a follow up required?
3		M	Medium	72
4		L	Low	120
5	Case no.	Date Created	Date Closed	Severity	Created by	Client	Detail	Follow-up
6	9071	29-Sep-08	01-Oct-08	H	Graeme	DIFC Global	Setting up Crispin for IPW sign off
7	9446	09-Oct-08	*	H	Tracey	XBOX	Set up of SSH for IPW
8	9499	10-Oct-08	*	H	Graeme	Switch	Change of payslip design in line with other Switch companies
Sheet1

Final part to my workbook.
As this is sheet 1, i want it to contain all cases (open/closed)
However:
In sheet 2 i want all open cases to show
In sheet 3 i want all closed cases to show

so if i closed a case in sheet 1 then it moves from sheet 2 to sheet 3. And obviously stays in sheet 1.
Hope you all get where im coming from?!?

Thanks again

 

[VoG]

Is the date closed being entered manually or using the event code I gave you earlier? If using code, please post the code that you now have.

 

[markjohnson002]

The case closed will be entered manually.

 

[VoG]

I suggest that you try this with a copy of your workbook. This replaces the code that I gave you earlier:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Found As Range, LR As Long
If Target.Column = 1 Then
    Application.EnableEvents = False
    Target.Offset(0, 1).Value = Now
    Application.EnableEvents = True
ElseIf Target.Column = 3 Then
    If IsDate(Target.Value) Then
        Set Found = Sheets("Sheet2").Columns("A").Find(what:=Target.Offset(0, -2).Value)
        If Not Found Is Nothing Then
            LR = Sheets("Sheet3").Range("A" & Rows.Count).End(xlUp).Row
            Found.EntireRow.Copy Destination:=Sheets("Sheet3").Range("A" & LR + 1)
            Application.CutCopyMode = False
            Found.EntireRow.Delete
        End If
    End If
End If
End Sub