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!

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!

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!

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!

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