Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting, Date
I have a spreadsheet for work orders. Column A is the work order number and
Column I is the Submit Date.... I want a conditional format so that when someone enters a work order number the date automatically fills in with the current date. I'm sure it's a TODAY function, but I can't seem to make it work. Can anyone help? Thanks! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting, Date
The TODAY function will update each day you open the workbook so I don't think
that is what you want. You need event code to enter a static date when the WO # is entered. Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Target, Columns("B:B")) Is Nothing Then On Error GoTo endit Application.EnableEvents = False If IsNumeric(Target) Then Target.Offset(0, 1).Value = Format(Date, "dd/mm/yyyy") End If End If endit: Application.EnableEvents = True End Sub This is sheet event code. Right-clcik on the sheet tab and "View Code" Copy/paste the above into that sheet module. Alt + q to return to the Excel window. Assumes your ID numbers are real numbers. If not, get rid of the "If IsNumeric(Target) Then" and one of the "End If"'s Gord Dibben MS Excel MVP On Mon, 7 Jul 2008 15:38:18 -0700, chickalina wrote: I have a spreadsheet for work orders. Column A is the work order number and Column I is the Submit Date.... I want a conditional format so that when someone enters a work order number the date automatically fills in with the current date. I'm sure it's a TODAY function, but I can't seem to make it work. Can anyone help? Thanks! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting, Date
That worked like a charm... a tweak here and there with some of the code and
VOILA!!!!.... success! "Gord Dibben" wrote: The TODAY function will update each day you open the workbook so I don't think that is what you want. You need event code to enter a static date when the WO # is entered. Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Target, Columns("B:B")) Is Nothing Then On Error GoTo endit Application.EnableEvents = False If IsNumeric(Target) Then Target.Offset(0, 1).Value = Format(Date, "dd/mm/yyyy") End If End If endit: Application.EnableEvents = True End Sub This is sheet event code. Right-clcik on the sheet tab and "View Code" Copy/paste the above into that sheet module. Alt + q to return to the Excel window. Assumes your ID numbers are real numbers. If not, get rid of the "If IsNumeric(Target) Then" and one of the "End If"'s Gord Dibben MS Excel MVP On Mon, 7 Jul 2008 15:38:18 -0700, chickalina wrote: I have a spreadsheet for work orders. Column A is the work order number and Column I is the Submit Date.... I want a conditional format so that when someone enters a work order number the date automatically fills in with the current date. I'm sure it's a TODAY function, but I can't seem to make it work. Can anyone help? Thanks! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting, Date
Good to hear.
Thanks for the feedback. Gord On Tue, 8 Jul 2008 08:24:01 -0700, chickalina wrote: That worked like a charm... a tweak here and there with some of the code and VOILA!!!!.... success! "Gord Dibben" wrote: The TODAY function will update each day you open the workbook so I don't think that is what you want. You need event code to enter a static date when the WO # is entered. Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Target, Columns("B:B")) Is Nothing Then On Error GoTo endit Application.EnableEvents = False If IsNumeric(Target) Then Target.Offset(0, 1).Value = Format(Date, "dd/mm/yyyy") End If End If endit: Application.EnableEvents = True End Sub This is sheet event code. Right-clcik on the sheet tab and "View Code" Copy/paste the above into that sheet module. Alt + q to return to the Excel window. Assumes your ID numbers are real numbers. If not, get rid of the "If IsNumeric(Target) Then" and one of the "End If"'s Gord Dibben MS Excel MVP On Mon, 7 Jul 2008 15:38:18 -0700, chickalina wrote: I have a spreadsheet for work orders. Column A is the work order number and Column I is the Submit Date.... I want a conditional format so that when someone enters a work order number the date automatically fills in with the current date. I'm sure it's a TODAY function, but I can't seem to make it work. Can anyone help? Thanks! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting, Date
Hi!
you should try the NOW() function, something like this: = +IF(A1<"",NOW(),"") where A1 is the cell where the work order number. But I there's a disadvantage, you won't keep the date, because if you open the workbook another day, NOW() will update to the current date, and I don't think you want that to happen. You could copy-paste as values at the end of the day the dates of the work orders you entered that day. I'm sure there's a fancier way of doing this but this is simple enough. Regards, Juan M |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
conditional formatting for cell date to equal today's date | Excel Worksheet Functions | |||
Conditional Formatting - due date | Excel Worksheet Functions | |||
conditional formatting date | Excel Worksheet Functions | |||
conditional DATE formatting? | Excel Discussion (Misc queries) | |||
Conditional Formatting, date. | Excel Worksheet Functions |