Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B20" '<== change to suit
On Error GoTo ws_exit
Application.EnableEvents = False
With Target
If Not Intersect(Target, Me.Range("A20")) Is Nothing Then
If .Offset(0, 1).Value <> "" Then
If .Value > .Offset(0, 1).Value Then
MsgBox "Invalid date"
.Value = ""
End If
End If
ElseIf Not Intersect(Target, Me.Range("B20")) Is Nothing Then
If .Value <> "" Then
If .Value <= .Offset(0, -1).Value Then
MsgBox "Invalid date"
.Value = ""
End If
End If
End If
End With
ws_exit:
Application.EnableEvents = True
End Sub
'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.

Signature
---
HTH
Bob
(there's no email, no snail mail, but somewhere should be gmail in my addy)
> Excel 2003 under Windows XP SP2
>
[quoted text clipped - 7 lines]
>
> Barry
Barry - 16 Oct 2007 18:32 GMT
Well I tried Bob's approach but it still needs tweaking. If anyone wants to
look at the worksheet, let me know!
Thanks
Barry
Gord Dibben - 16 Oct 2007 19:07 GMT
Bob's approach works for me.
What "tweaking" do you think it needs?
Data Validation won't work with the date picker or a copied date.
Gord
>Well I tried Bob's approach but it still needs tweaking. If anyone wants to
>look at the worksheet, let me know!
>
>Thanks
>
>Barry
Barry - 16 Oct 2007 19:33 GMT
I think his code was just what I was looking for. I was just confused as to
how to apply it to the rest of the worksheet. I will be happy to post the
sheet if anyone wants to give it a looksee! It's the only way I can explain.
Barry
Gord Dibben - 16 Oct 2007 20:05 GMT
Use either of the 2 free filehosts below to upload a sample of your actual
worksheet.
(Desensitize it first, if needed). Then copy & paste the generated link to
your sample file in response here:
http://www.flypicture.com/
http://cjoint.com/index.php
Gord
>I think his code was just what I was looking for. I was just confused as to
>how to apply it to the rest of the worksheet. I will be happy to post the
>sheet if anyone wants to give it a looksee! It's the only way I can explain.
>
>Barry
Barry - 16 Oct 2007 21:11 GMT
Ok here it is, althow I couldn't read the page I figured it out!
This link is where you can find the whorsheet/workbook I need a bit of help
with!
http://cjoint.com/?kqwhz1cAZI
Thanks,
Barry
Bob Phillips - 16 Oct 2007 21:28 GMT
http://cjoint.com/?kqwCOJx8oI

Signature
---
HTH
Bob
(there's no email, no snail mail, but somewhere should be gmail in my addy)
> Ok here it is, althow I couldn't read the page I figured it out!
>
[quoted text clipped - 6 lines]
>
> Barry
Gord Dibben - 16 Oct 2007 21:52 GMT
Have downloaded and saved the workbook.
See you have adjusted target range but that doesn't create a problem in my
estimation.
If a bad date <date in A20:A23 is entered in B2:B23 you get the "Invalid Date"
message and date is not entered.
What tweaking do you specifically need doing?
Gord
>Ok here it is, althow I couldn't read the page I figured it out!
>
[quoted text clipped - 6 lines]
>
>Barry
Gord Dibben - 16 Oct 2007 22:02 GMT
Looks like Bob already tweaked it.
http://cjoint.com/?kqwCOJx8oI
That's the one I mistakenly downloaded.
Gord
>Have downloaded and saved the workbook.
>
[quoted text clipped - 18 lines]
>>
>>Barry
Barry - 16 Oct 2007 22:50 GMT
GREAT BIG THANKS!!! To all who responded.
Bob Philips and Gord Dibben I hope you two realize what a super job you do
here on a regular basis.
I look foward to having another hickup for you guys to help me with....LOL
Barry
Gord Dibben - 16 Oct 2007 23:05 GMT
Thanks for the thanks.
We all await with bated breath for your next hiccup<g>
Gord
>GREAT BIG THANKS!!! To all who responded.
>
[quoted text clipped - 3 lines]
>
>Barry
Select B20
Use Data | Validation, and select Date from the Allow options
Now in the Setting tab specify: Date: Greater Than" Start date A20
best wishes

Signature
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email
> Excel 2003 under Windows XP SP2
>
[quoted text clipped - 7 lines]
>
> Barry
Gord Dibben - 16 Oct 2007 17:45 GMT
Bernard
Excel 2003 still allows a "less than" date to be entered when using the date
picker.
Manually entered dates will trigger the DV warning as advertised.
Gord Dibben MS Excel MVP
>Select B20
>Use Data | Validation, and select Date from the Allow options
>Now in the Setting tab specify: Date: Greater Than" Start date A20
>best wishes
Bernard Liengme - 16 Oct 2007 17:49 GMT
Oh dear, I missed the 'date picker' bit. I was imagining someone typing.
Must read questions more carefully!
thanks

Signature
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email
> Bernard
>
[quoted text clipped - 10 lines]
>>Now in the Setting tab specify: Date: Greater Than" Start date A20
>>best wishes
You can either use the data/validation wizard to achieve this or
use vb to monitor this range
the wizard is easier