Thanks to all of you for your rapid responses. My first piece of VBA works
like a charm. What a thrill!!!!!!! This could be addicting. If you could
have one book on hand as a reference guide, which one would you pick?

"Don Guillett" wrote:

Or,
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Columns("f")) Is Nothing Then Exit Sub
If IsDate(Target.Value) And Target.Value 0 Then
target.offset(,-2)= InputBox("Enter Obligated Amount", "Input", 1)
End If
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"ShaneDevenshire" wrote in
message ...
Hi,

Just change the F1:F39 reference so its ready for as many cells as you
want.

or if all the cells down the the new row will have data you could modify
the
code to determine the row with the last data and increase the range by
one.

Private Sub Worksheet_Change(ByVal Target As Range)
LastRow = Range("F1").End(XLDown).Row+1
If Not Application.Intersect(Range("F1:F"&LastRow), Target) Is Nothing
Then
If IsDate(Target.Value) And Target.Value 0 Then
target.offset(,-2)= InputBox("Enter Obligated Amount", "Input", 1)
End If
End If
End Sub

--
Thanks,
Shane Devenshire


"Frappier" wrote:

By saying the range is F1:F39 am I going to have a problem if rows are
added? I would like this to apply to the new rows also.


"Don Guillett" wrote:

Try

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Range("F1:F39"), Target) Is Nothing
Then
If IsDate(Target.Value) And Target.Value 0 Then
target.offset(,-2)= InputBox("Enter Obligated Amount", "Input", 1)
End If
End If
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Frappier" wrote in message
...

I am new to vba. I am trying to create a macro that once I have
entered a
date in any cell in column E, it will take me to column c (on the
same
row)
to enter a dollar amount. So far I have created this macro

Sub test2()
NumberToBeInput = InputBox("Enter Obligated Amount", "Input", 1)
ActiveCell.Offset(0, -2).Select
ActiveCell.Value = NumberToBeInput
End Sub

and then on the sheet I have created the following

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Range("F1:F39"), Target) Is Nothing
Then
If IsDate(Target.Value) And Target.Value 0 Then
test2
End If
End If
End Sub

Please help.