Excel 2003 Private Sub Not Working

[RobL]

I have a spreadsheet in which I have a number of columns where information is entered. What I’m trying to achieve is that when a valuein one column is chosen an answer is forced to be entered in an adjacent columnand and that no further information can be entered until this column is filled. The columns in question are D and E. In the D column there is a drop down list from which a response is either ‘YES’ or ‘NO’. If ‘NO’ is chosen a reason has to be entered into the adjacent cell, e.g if ‘NO’ is entered in Cell D3 a reason MUST BE entered in E3 before any further cells can be completed. The code I’m using at the moment and doesn’t seem to be working is as follows:

Private Sub Worksheet_SelectionChange(ByVal Target AsRange)
Dim myCell As Range
Dim myRange As Range
On Error GoTo NoRange
If Range("D3:102").Value = “NO” Then
If Range(“E3:E102”). Value = “########” then (I’massuming I’m using the right symbol for text)
Exit Sub
Set myRange = Range("E3:E102")
For Each myCell In Range("D3:102")
If myCell.Value = "NO" Then
If myRange.Value =”” then
MsgBox (“You must enter a reason why a 48 Hour Responsehas not been sent”), vbCritical, “Reason for Non-Dispatch Required”
Application.EnableEvents = False
myCell.End(xlToRight).Offset(0,1).select
Application.EnableEvents = True
Exit Sub
End If
Next myCell
NoRange:
Application.EnableEvents = True
End if
End If
End Sub

Many thanks in advance for your help

[JosephP]

perhaps

Please Login or Register  to view this content.

[RobL]

JosephP many thanks this worked a treat.

Very much appreciated.