Tom,

It is working fine till now.

Now I've come up with one mroe problem. Could you tell me how to find if the
cell's valu is Non Date.

I'm trying this with Select Case event like below,

If Target.Column = 18 Then
Me.Unprotect Password:="123"
Select Case (Target)
Case "":
Cells(Target.Row, 34).Value = "NOT OFFERED"
Case "NR":
If Cells(Target.Row, 8).Value = "REQ." Then
Msg = "SORRY. ONLY DATE VALUE CAN BE ENTERED. ENTER THE
VALUE IN THE BELOW FORMAT." & vbNewLine & " MM/DD/YYYY "
MsgBox Msg, vbCritical, "INVALID ENTRY"
Target.Value = ""
End If
Case Is < Cells(Target.Row, 17).Value:
Msg = "SORRY. ENTERED DATE SHOULD BE GREATER THAN OR
EQUAL TO THE INTERNAL INSPECTION PASSED DATE." & vbNewLine & " KINDLY ENTER A
NEW DATE IN THIS FORMAT 'MM/DD/YYYY' ."
MsgBox Msg, vbCritical, "INVALID ENTRY"
Target.Value = ""
Case Is = Cells(Target.Row, 17).Value:
Cells(Target.Row, 20).Locked = False
Cells(Target.Row, 34).Value = "NOT INSPECTED"
Case Else:
Msg = "SORRY. ONLY DATE VALUE CAN BE ENTERED. ENTER THE
VALUE IN THE BELOW FORMAT." & vbNewLine & " MM/DD/YYYY "
MsgBox Msg, vbCritical, "INVALID ENTRY"
Target.Value = ""
End Select
Me.Protect Password:="123", AllowFiltering:=True
End If

Now the problem is, if I entered some text values (means string values, like
A, B or something else), it is taking as such without showing any error and
updating the 34th column as Not Inspected.

I want to find out if the value of the cell is String, then my witten error
msg should dispaly.

Please tell me is there anything available for find string like IsDate,
IsNumeric etc.

Sriram.

"Tom Ogilvy" wrote:

Try modifying your code like this

Private Sub Worksheet_Change(ByVal Target As Range)
On Error goto ErrHandler
Application.EnableEvents = False

' your existing code

ErrHandler:
if err.Number < 0 then
msgbox "Error " & err.Number & "has occured" &
vbnewline & err.Description
end if
Err.clear
Application.EnableEvents = True
End Sub

--
Regards,
Tom Ogilvy