Validation Data

[grache]

I have a worksheet that contains one column where an amount is entered if an offer is made ($). The next column contains the date the offer was made.
I want to set up validation rules in the date column to insist that whenever an amount is placed in the offer column, a date must be placed in the date column. Only some rows contain offers.
Thanks.

[Tom Schreiner]

Hi Grache...
What are your columns?
Offer = ?
Date = ?
Thanks,
Tom

[grache]

Hi Tom
column J contains the settlement amount offered (ie $10,000). Column K contains the date the offer was made (ie 19/04/02)
Thanks

On 2002-04-24 00:08, TsTom wrote:
Hi Grache...
What are your columns?
Offer = ?
Date = ?
Thanks,
Tom

[Tom Schreiner]

Hi Grache.
This code will force the issue.
If any change is detected in the
offer column, the code will check
for a valid numeric amount. If not
valid, a message will appear warning
the user to enter a valid numeric
amount and focus will be reurned to
that cell. If the number is valid, then
a message will warn the user to enter a
date in the date column. No exit from
the cell will be allowed until a valid date
is entered.
To use this procedure, simply right-click
on your sheet tab, choose "View Code", and
then paste this in the window which now appears.
Close this window and try and enter some text in
the Offer column.

Tom

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 10 Then
If Not IsNumeric(Target) Then
MsgBox "Numeric amount expected. Please Re-Enter"
Target.Select
Exit Sub
End If
Cells(Target.Row, Target.Column + 1).Select
MsgBox "Please enter a date in the selected cell"
Application.EnableEvents = False
Do Until IsDate(Cells(Target.Row, Target.Column + 1).Value)
DoEvents
If ActiveCell.Address <> Cells(Target.Row, Target.Column + 1).Address And _
Not IsDate(Cells(Target.Row, Target.Column + 1).Value) Then
MsgBox "A date must be entered to continue"
Cells(Target.Row, Target.Column + 1).Select
End If
Loop
Application.EnableEvents = True
End If
End Sub