Insert a day on change of other cell
 

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

Insert a day on change of other cell
 

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

Insert a day on change of other cell
 

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

Insert a day on change of other cell
 

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

Insert a day on change of other cell
 

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

Insert a day on change of other cell
 

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