Auto Dates
 

I am using Excel 2000 and this code in sheet 1 and it works fine. What I want
to do is change the range to A2 to M6. When I replace "A" with "A1:M6" it
will not work. Can anyone help me with this?

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'when entering data in a cell in Col A
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 1 Then
n = Target.Row
If IsNumeric(Excel.Range("A" & n).Value) And _
Excel.Range("A" & n).Value 1 Then
Excel.Sheets(2).Range("A" & n).Value = "paid" & Format(Now, "
mm/dd")
End If
End If
enditall:
Application.EnableEvents = True
End Sub

Auto Dates
 

I'm thinking that I don't really understand what you want to do, but I'll
take a pop anyway

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'when entering data in a cell in Col A
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 1 Then
n = Target.Row
For Each cell In Me.Range("A2:M6")
If IsNumeric(cell.Value) Then
If cell.Value 1 Then
Worksheets(2).Range(cell.Address(0, 0)).Value _
= "paid" & Format(Now, "mm/dd")
End If
End If
Next cell
End If
enditall:
Application.EnableEvents = True
End Sub

or alternatively

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'when entering data in a cell in Col A
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 1 Then
If Not Intersect(Target, Me.Range("A2:M6")) Is Nothing Then
If IsNumeric(Target.Value) Then
If Target.Value 1 Then
Worksheets(2).Range(Target.Address(0, 0)).Value _
= "paid" & Format(Now, "mm/dd")
End If
End If
Next cell
End If
enditall:
Application.EnableEvents = True
End Sub



--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Merle" wrote in message
...
I am using Excel 2000 and this code in sheet 1 and it works fine. What I
want
to do is change the range to A2 to M6. When I replace "A" with "A1:M6" it
will not work. Can anyone help me with this?

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'when entering data in a cell in Col A
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 1 Then
n = Target.Row
If IsNumeric(Excel.Range("A" & n).Value) And _
Excel.Range("A" & n).Value 1 Then
Excel.Sheets(2).Range("A" & n).Value = "paid" & Format(Now, "
mm/dd")
End If
End If
enditall:
Application.EnableEvents = True
End Sub

Auto Dates
 

Thanks Bob that did the trick. I did have to delete these two lines.

If Target.Cells.Column = 1 Then
n = Target.Row

It would only put the paid date in (Col. A) of Sheet 2 but without those two
lines it works great.

Again Thanks for all your help.

Merle

"Bob Phillips" wrote:

I'm thinking that I don't really understand what you want to do, but I'll
take a pop anyway

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'when entering data in a cell in Col A
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 1 Then
n = Target.Row
For Each cell In Me.Range("A2:M6")
If IsNumeric(cell.Value) Then
If cell.Value 1 Then
Worksheets(2).Range(cell.Address(0, 0)).Value _
= "paid" & Format(Now, "mm/dd")
End If
End If
Next cell
End If
enditall:
Application.EnableEvents = True
End Sub

or alternatively

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'when entering data in a cell in Col A
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 1 Then
If Not Intersect(Target, Me.Range("A2:M6")) Is Nothing Then
If IsNumeric(Target.Value) Then
If Target.Value 1 Then
Worksheets(2).Range(Target.Address(0, 0)).Value _
= "paid" & Format(Now, "mm/dd")
End If
End If
Next cell
End If
enditall:
Application.EnableEvents = True
End Sub



--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Merle" wrote in message
...
I am using Excel 2000 and this code in sheet 1 and it works fine. What I
want
to do is change the range to A2 to M6. When I replace "A" with "A1:M6" it
will not work. Can anyone help me with this?

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'when entering data in a cell in Col A
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 1 Then
n = Target.Row
If IsNumeric(Excel.Range("A" & n).Value) And _
Excel.Range("A" & n).Value 1 Then
Excel.Sheets(2).Range("A" & n).Value = "paid" & Format(Now, "
mm/dd")
End If
End If
enditall:
Application.EnableEvents = True
End Sub

Auto Dates
 

Okay, I see your intent better now. Glad you are sorted.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Merle" wrote in message
...
Thanks Bob that did the trick. I did have to delete these two lines.

If Target.Cells.Column = 1 Then
n = Target.Row

It would only put the paid date in (Col. A) of Sheet 2 but without those
two
lines it works great.

Again Thanks for all your help.

Merle

"Bob Phillips" wrote:

I'm thinking that I don't really understand what you want to do, but I'll
take a pop anyway

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'when entering data in a cell in Col A
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 1 Then
n = Target.Row
For Each cell In Me.Range("A2:M6")
If IsNumeric(cell.Value) Then
If cell.Value 1 Then
Worksheets(2).Range(cell.Address(0, 0)).Value _
= "paid" & Format(Now, "mm/dd")
End If
End If
Next cell
End If
enditall:
Application.EnableEvents = True
End Sub

or alternatively

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'when entering data in a cell in Col A
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 1 Then
If Not Intersect(Target, Me.Range("A2:M6")) Is Nothing Then
If IsNumeric(Target.Value) Then
If Target.Value 1 Then
Worksheets(2).Range(Target.Address(0, 0)).Value _
= "paid" & Format(Now, "mm/dd")
End If
End If
Next cell
End If
enditall:
Application.EnableEvents = True
End Sub



--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Merle" wrote in message
...
I am using Excel 2000 and this code in sheet 1 and it works fine. What I
want
to do is change the range to A2 to M6. When I replace "A" with "A1:M6"
it
will not work. Can anyone help me with this?

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'when entering data in a cell in Col A
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 1 Then
n = Target.Row
If IsNumeric(Excel.Range("A" & n).Value) And _
Excel.Range("A" & n).Value 1 Then
Excel.Sheets(2).Range("A" & n).Value = "paid" & Format(Now,
"
mm/dd")
End If
End If
enditall:
Application.EnableEvents = True
End Sub