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.