Hi

I am trying to get spreadsheet to input todays date into a cell if an other
cell has a Y char in there. Thanks to many of you and to Frank I found I can
use UDF and I took code from this site and modified it.

The problem is this. I need it to trigger if the user puts in "Y" or "y" and
to ignore anything else inc blancks and "n" or "N".

This is the code that I am using:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myCell As Range
If Intersect(Target, Range("J9:J109")) = "Y" Then
Application.EnableEvents = False
For Each myCell In Intersect(Target, Range("J9:J109"))
myCell.Offset(0, 4).Value = (Date)
Next myCell
Application.EnableEvents = True
End If
If Not Intersect(Target, Range("J9:J109")) < "y" Then
Application.EnableEvents = False
For Each myCell In Intersect(Target, Range("J9:J109"))
myCell.Offset(0, 4).Value = (Date)
Next myCell
Application.EnableEvents = True
End If
End Sub

I am sure there is better way in doing the IF statment to check for both cap
and lower Y's.

Help!

Thanks

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("j9:j109")) Is Nothing Then
Application.EnableEvents = False
If UCase(Target) = "Y" Then Target.Offset(, 4) = Date
End If
Application.EnableEvents = True
End Sub


--
Don Guillett
SalesAid Software

"Namster" wrote in message
...
Hi

I am trying to get spreadsheet to input todays date into a cell if an
other
cell has a Y char in there. Thanks to many of you and to Frank I found I
can
use UDF and I took code from this site and modified it.

The problem is this. I need it to trigger if the user puts in "Y" or "y"
and
to ignore anything else inc blancks and "n" or "N".

This is the code that I am using:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myCell As Range
If Intersect(Target, Range("J9:J109")) = "Y" Then
Application.EnableEvents = False
For Each myCell In Intersect(Target, Range("J9:J109"))
myCell.Offset(0, 4).Value = (Date)
Next myCell
Application.EnableEvents = True
End If
If Not Intersect(Target, Range("J9:J109")) < "y" Then
Application.EnableEvents = False
For Each myCell In Intersect(Target, Range("J9:J109"))
myCell.Offset(0, 4).Value = (Date)
Next myCell
Application.EnableEvents = True
End If
End Sub

I am sure there is better way in doing the IF statment to check for both
cap
and lower Y's.

Help!

Thanks

Easiest way is to read them all as uppercase such as UCase(cells(1,1) this
will ensure you are checking apples to apples
--
-John Northwest11
Please rate when your question is answered to help us and others know what
is helpful.


"Namster" wrote:

Hi

I am trying to get spreadsheet to input todays date into a cell if an other
cell has a Y char in there. Thanks to many of you and to Frank I found I can
use UDF and I took code from this site and modified it.

The problem is this. I need it to trigger if the user puts in "Y" or "y" and
to ignore anything else inc blancks and "n" or "N".

This is the code that I am using:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myCell As Range
If Intersect(Target, Range("J9:J109")) = "Y" Then
Application.EnableEvents = False
For Each myCell In Intersect(Target, Range("J9:J109"))
myCell.Offset(0, 4).Value = (Date)
Next myCell
Application.EnableEvents = True
End If
If Not Intersect(Target, Range("J9:J109")) < "y" Then
Application.EnableEvents = False
For Each myCell In Intersect(Target, Range("J9:J109"))
myCell.Offset(0, 4).Value = (Date)
Next myCell
Application.EnableEvents = True
End If
End Sub

I am sure there is better way in doing the IF statment to check for both cap
and lower Y's.

Help!

Thanks

You're a star thank you.


"Don Guillett" wrote:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("j9:j109")) Is Nothing Then
Application.EnableEvents = False
If UCase(Target) = "Y" Then Target.Offset(, 4) = Date
End If
Application.EnableEvents = True
End Sub


--
Don Guillett
SalesAid Software

"Namster" wrote in message
...
Hi

I am trying to get spreadsheet to input todays date into a cell if an
other
cell has a Y char in there. Thanks to many of you and to Frank I found I
can
use UDF and I took code from this site and modified it.

The problem is this. I need it to trigger if the user puts in "Y" or "y"
and
to ignore anything else inc blancks and "n" or "N".

This is the code that I am using:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myCell As Range
If Intersect(Target, Range("J9:J109")) = "Y" Then
Application.EnableEvents = False
For Each myCell In Intersect(Target, Range("J9:J109"))
myCell.Offset(0, 4).Value = (Date)
Next myCell
Application.EnableEvents = True
End If
If Not Intersect(Target, Range("J9:J109")) < "y" Then
Application.EnableEvents = False
For Each myCell In Intersect(Target, Range("J9:J109"))
myCell.Offset(0, 4).Value = (Date)
Next myCell
Application.EnableEvents = True
End If
End Sub

I am sure there is better way in doing the IF statment to check for both
cap
and lower Y's.

Help!

Thanks

excellent thank you


"John Bundy" wrote:

Easiest way is to read them all as uppercase such as UCase(cells(1,1) this
will ensure you are checking apples to apples
--
-John Northwest11
Please rate when your question is answered to help us and others know what
is helpful.


"Namster" wrote:

Hi

I am trying to get spreadsheet to input todays date into a cell if an other
cell has a Y char in there. Thanks to many of you and to Frank I found I can
use UDF and I took code from this site and modified it.

The problem is this. I need it to trigger if the user puts in "Y" or "y" and
to ignore anything else inc blancks and "n" or "N".

This is the code that I am using:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myCell As Range
If Intersect(Target, Range("J9:J109")) = "Y" Then
Application.EnableEvents = False
For Each myCell In Intersect(Target, Range("J9:J109"))
myCell.Offset(0, 4).Value = (Date)
Next myCell
Application.EnableEvents = True
End If
If Not Intersect(Target, Range("J9:J109")) < "y" Then
Application.EnableEvents = False
For Each myCell In Intersect(Target, Range("J9:J109"))
myCell.Offset(0, 4).Value = (Date)
Next myCell
Application.EnableEvents = True
End If
End Sub

I am sure there is better way in doing the IF statment to check for both cap
and lower Y's.

Help!

Thanks

Glad to help. I hope you know that this takes care of both Y and y.

--
Don Guillett
SalesAid Software

"Namster" wrote in message
...
You're a star thank you.


"Don Guillett" wrote:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("j9:j109")) Is Nothing Then
Application.EnableEvents = False
If UCase(Target) = "Y" Then Target.Offset(, 4) = Date
End If
Application.EnableEvents = True
End Sub


--
Don Guillett
SalesAid Software

"Namster" wrote in message
...
Hi

I am trying to get spreadsheet to input todays date into a cell if an
other
cell has a Y char in there. Thanks to many of you and to Frank I found
I
can
use UDF and I took code from this site and modified it.

The problem is this. I need it to trigger if the user puts in "Y" or
"y"
and
to ignore anything else inc blancks and "n" or "N".

This is the code that I am using:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myCell As Range
If Intersect(Target, Range("J9:J109")) = "Y" Then
Application.EnableEvents = False
For Each myCell In Intersect(Target, Range("J9:J109"))
myCell.Offset(0, 4).Value = (Date)
Next myCell
Application.EnableEvents = True
End If
If Not Intersect(Target, Range("J9:J109")) < "y" Then
Application.EnableEvents = False
For Each myCell In Intersect(Target, Range("J9:J109"))
myCell.Offset(0, 4).Value = (Date)
Next myCell
Application.EnableEvents = True
End If
End Sub

I am sure there is better way in doing the IF statment to check for
both
cap
and lower Y's.

Help!

Thanks