LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3
Default 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


 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Can cell format come from and change with reference cell format jclouse Excel Discussion (Misc queries) 1 November 29th 06 03:20 AM
Using an offset formula for the reference in a relative reference Cuda Excel Worksheet Functions 6 November 15th 06 05:12 PM
I Need a formula to evaluate a cell with + or - values Bob in Oklahoma Excel Worksheet Functions 6 October 31st 05 02:41 PM
Cell Change Color - Need Help alani New Users to Excel 3 June 29th 05 03:50 PM
GET.CELL Biff Excel Worksheet Functions 2 November 24th 04 07:16 PM


All times are GMT +1. The time now is 02:29 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"