Visual Basic - Not allowing cell entry

**emad1001** · 08-20-2008, 03:21 PM

Hello again,

So I am still working on the same sheet and my question is as follows:

I have a cell that I do no want anyone to enter any information into unless a previous cell has "U" inside of it. I know, I know confusing.

Okay so so I have a column of cells named "Fringe Benefit Type" you can choose different letters to corropand to different types. Now I have set the letter "U" to be the one I want and that works. Now the next cell is called "Hire Season". Now (stay with me here) IF "Fringe Benefit Type" is equal to "U", I don't want the user to be able to enter anything into "Hire Season".

I am using the visual basic thing, which already has some code, but I am not understanding it too well.
Any help would be appreciated.
Reply With Quote

**shg** · 08-20-2008, 03:28 PM

How about posting the code, and explaining what happens that you don't like?

**emad1001** · 08-20-2008, 03:32 PM

Originally Posted by shg

How about posting the code, and explaining what happens that you don't like?

Thats the thing, I don't know much at all about the code, so I was hoping that someone could get me started...

**shg** · 08-20-2008, 03:56 PM

What column is "Fringe Benefit Type" in? What column is "Hire Season"?

You're aware that if you use code to modify a worksheet, the Undo stack is flushed, and that's OK?

**emad1001** · 08-20-2008, 04:03 PM

Originally Posted by shg

What column is "Fringe Benefit Type" in? What column is "Hire Season"?

You're aware that if you use code to modify a worksheet, the Undo stack is flushed, and that's OK?

Fringe Benefit is Starts at cell P23 thru P62. Hire Season is R23-R62.

That is fine,
Thanks!

**shg** · 08-20-2008, 04:41 PM

Right-click on the sheet tab and paste this in the window that opens:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim r       As Range
    Dim cell    As Range

    Set r = Intersect(Target, Rows("23:62"), Union(Columns("P"), Columns("R")))
    If r Is Nothing Then Exit Sub

    For Each cell In r
        With cell
            If .Column = Columns("P").Column Then
                If .Value = "U" And Not IsEmpty(Cells(.Row, "R").Value) Then
                    With Cells(.Row, "R")
                        .ClearContents
                        .Select
                        MsgBox "Must be blank.", vbOKOnly
                    End With
                End If

            Else    ' it's in column R
                If Cells(.Row, "P").Value = "U" And Not IsEmpty(.Value) Then
                    .ClearContents
                    .Select
                    MsgBox "Must be blank.", vbOKOnly
                End If
            End If
        End With
    Next cell
End Sub

**emad1001** · 08-20-2008, 05:47 PM

How do I change it so that it does not have a Worksheet_Change event?

**shg** · 08-20-2008, 05:49 PM

2. Please read the Forum Rules and then edit your post to wrap your code with Code Tags.

How do I change it so that it does not have a Worksheet_Change event?

You have to merge the code in the two subs together so that the single event does everything you need done.

**emad1001** · 08-20-2008, 05:52 PM

Okay, done

**shg** · 08-20-2008, 05:54 PM

Thank you.

Did you write the other worksheet change code?

**emad1001** · 08-20-2008, 06:07 PM

I honestly don't know how. I need to be walked thru this I think. So the code is appearing exactly like the following:

Private Sub Worksheet_Activate()

End Sub

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'this is all for validation of user input on the details sheet

'make sure a date has been input in E11 and E12
If Target.Cells(1, 1).Address(0, 0) = "E11" Then
    If Target.Cells(1, 1) <> "" And Not IsDate(Target.Cells(1, 1)) Then
        MsgBox "You must input a date!", vbExclamation, "DATE REQUIRED"
        Target.Cells(1, 1) = ""
        Exit Sub
    End If
End If
If Target.Cells(1, 1).Address(0, 0) = "E12" Then
    If Target.Cells(1, 1) <> "" And Not IsDate(Target.Cells(1, 1)) Then
        MsgBox "You must input a valid date!", vbExclamation, "DATE REQUIRED"
        Target.Cells(1, 1) = ""
        Exit Sub
    End If
End If

'make sure enddate>=startdate
If Sheets("Salary Detail").Range("enddate") <> "" And Sheets("Salary Detail").Range("startdate") <> "" Then
    If Sheets("Salary Detail").Range("enddate") < Sheets("Salary Detail").Range("startdate") Then
        MsgBox "We've heard of short projects before, but this one really takes the cake!", vbExclamation, "END DATE BEFORE START DATE"
        Sheets("Salary Detail").Range("enddate") = ""
        Exit Sub
    End If
End If
    
'make sure enddate<=startdate + 366
If Sheets("Salary Detail").Range("enddate") <> "" And Sheets("Salary Detail").Range("startdate") <> "" Then
    If Sheets("Salary Detail").Range("enddate") > Sheets("Salary Detail").Range("startdate") + 366 Then
        MsgBox "This project has a very long initial year!", vbExclamation, "END DATE BEFORE START DATE"
        Sheets("Salary Detail").Range("enddate") = ""
        Exit Sub
    End If
End If

'make sure FTE's in C23:C62  and the levels of effort in H23:H62 are in the range 0-1
If Target.Column = 3 Or Target.Column = 8 Then
    If Target.Row >= 23 And Target.Row <= 62 Then
        If Not IsNumeric(Target.Cells(1, 1)) Then
            MsgBox "You must enter a number between 0 and 1!", vbExclamation, "NUMBER BETWEEN 0 AND 1 REQUIRED"
            Target.Cells(1, 1) = ""
            Exit Sub
        ElseIf Target.Cells(1, 1) < 0 Or Target.Cells(1, 1) > 1 Then
            MsgBox "You must enter a number between 0 and 1!", vbExclamation, "NUMBER BETWEEN 0 AND 1 REQUIRED"
            Target.Cells(1, 1) = ""
            Exit Sub
        End If
    End If
End If

'make sure the salaries in E23:E62 are positive numbers
If Target.Column = 5 Then
    If Target.Row >= 23 And Target.Row <= 62 Then
        If Not IsNumeric(Target.Cells(1, 1)) Then
            MsgBox "You must enter a number!", vbExclamation, "NUMBER GREATER THAN 0 REQUIRED"
            Target.Cells(1, 1) = ""
            Exit Sub
        ElseIf Target.Cells(1, 1) < 0 Then
            MsgBox "You're getting paid that badly!?", vbExclamation, "NUMBER GREATER THAN 0 REQUIRED"
            Target.Cells(1, 1) = ""
            Exit Sub
        End If
    End If
End If

'make sure the fringe benefit types in P23:P62 are appropriate
If Target.Column = 16 Then
    If Target.Row >= 23 And Target.Row <= 62 Then
        If Target.Cells(1, 1) <> "" And Target.Cells(1, 1) <> "F" And Target.Cells(1, 1) <> "U" And Target.Cells(1, 1) <> "A" And Target.Cells(1, 1) <> "P" And Target.Cells(1, 1) <> "T" And Target.Cells(1, 1) <> "R" And Target.Cells(1, 1) <> "S" And Target.Cells(1, 1) <> "W" And Target.Cells(1, 1) <> "f" And Target.Cells(1, 1) <> "a" And Target.Cells(1, 1) <> "p" And Target.Cells(1, 1) <> "t" And Target.Cells(1, 1) <> "r" And Target.Cells(1, 1) <> "u" And Target.Cells(1, 1) <> "s" And Target.Cells(1, 1) <> "w" Then
            MsgBox "You must enter a fringe benefit code from the list above!", vbExclamation, "'F','A','P','T','R','S', OR 'W' REQUIRED"
            Target.Cells(1, 1) = ""
            Exit Sub
        End If
    End If
End If
 
    
    If Target.Row >= 23 And Target.Row <= 62 Then
'make sure the project duration in L10 is a positive number
If Target.Column = 12 And Target.Row = 10 Then
        If Not IsNumeric(Target.Cells(1, 1)) Then
            MsgBox "You must enter a number!", vbExclamation, "NUMBER GREATER THAN 0 REQUIRED"
            Target.Cells(1, 1) = ""
            Exit Sub
        ElseIf Target.Cells(1, 1) < 0 Then
            MsgBox "Are you sure the project is that short?", vbExclamation, "NUMBER GREATER THAN 0 REQUIRED"
            Target.Cells(1, 1) = ""
            Exit Sub
        End If
End If

    Dim r       As Range
    Dim cell    As Range

    Set r = Intersect(Target, Rows("23:62"), Union(Columns("P"), Columns("R")))
    If r Is Nothing Then Exit Sub

    For Each cell In r
        With cell
            If .Column = Columns("P").Column Then
                If .Value = "U" And Not IsEmpty(Cells(.Row, "R").Value) Then
                    With Cells(.Row, "R")
                        .ClearContents
                        .Select
                        MsgBox "Must be blank.", vbOKOnly
                    End With
                End If

            Else    ' it's in column R
                If Cells(.Row, "P").Value = "U" And Not IsEmpty(.Value) Then
                    .ClearContents
                    .Select
                    MsgBox "Must be blank.", vbOKOnly
                End If
            End If
        End With
    Next cell
End Sub

Please let me know exactly what I have to do to make it work. Thanks.

**shg** · 08-20-2008, 06:15 PM

Delete this, it's not doing anything:

Private Sub Worksheet_Activate()

End Sub

Change all the Exit Sub statements to

GoTo NextStep

Then add this line before my code:

 
NextStep: ' added line
    Dim r       As Range
    Dim cell    As Range

**emad1001** · 08-20-2008, 06:35 PM

This is what I have now:



Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'this is all for validation of user input on the details sheet

'make sure a date has been input in E11 and E12
If Target.Cells(1, 1).Address(0, 0) = "E11" Then
    If Target.Cells(1, 1) <> "" And Not IsDate(Target.Cells(1, 1)) Then
        MsgBox "You must input a date!", vbExclamation, "DATE REQUIRED"
        Target.Cells(1, 1) = ""
        GoTo NextStep
    End If
End If
If Target.Cells(1, 1).Address(0, 0) = "E12" Then
    If Target.Cells(1, 1) <> "" And Not IsDate(Target.Cells(1, 1)) Then
        MsgBox "You must input a valid date!", vbExclamation, "DATE REQUIRED"
        Target.Cells(1, 1) = ""
        GoTo NextStep
    End If
End If

'make sure enddate>=startdate
If Sheets("Salary Detail").Range("enddate") <> "" And Sheets("Salary Detail").Range("startdate") <> "" Then
    If Sheets("Salary Detail").Range("enddate") < Sheets("Salary Detail").Range("startdate") Then
        MsgBox "We've heard of short projects before, but this one really takes the cake!", vbExclamation, "END DATE BEFORE START DATE"
        Sheets("Salary Detail").Range("enddate") = ""
        GoTo NextStep
    End If
End If
    
'make sure enddate<=startdate + 366
If Sheets("Salary Detail").Range("enddate") <> "" And Sheets("Salary Detail").Range("startdate") <> "" Then
    If Sheets("Salary Detail").Range("enddate") > Sheets("Salary Detail").Range("startdate") + 366 Then
        MsgBox "This project has a very long initial year!", vbExclamation, "END DATE BEFORE START DATE"
        Sheets("Salary Detail").Range("enddate") = ""
        GoTo NextStep
    End If
End If

'make sure FTE's in C23:C62  and the levels of effort in H23:H62 are in the range 0-1
If Target.Column = 3 Or Target.Column = 8 Then
    If Target.Row >= 23 And Target.Row <= 62 Then
        If Not IsNumeric(Target.Cells(1, 1)) Then
            MsgBox "You must enter a number between 0 and 1!", vbExclamation, "NUMBER BETWEEN 0 AND 1 REQUIRED"
            Target.Cells(1, 1) = ""
            GoTo NextStep
        ElseIf Target.Cells(1, 1) < 0 Or Target.Cells(1, 1) > 1 Then
            MsgBox "You must enter a number between 0 and 1!", vbExclamation, "NUMBER BETWEEN 0 AND 1 REQUIRED"
            Target.Cells(1, 1) = ""
            GoTo NextStep
        End If
    End If
End If

'make sure the salaries in E23:E62 are positive numbers
If Target.Column = 5 Then
    If Target.Row >= 23 And Target.Row <= 62 Then
        If Not IsNumeric(Target.Cells(1, 1)) Then
            MsgBox "You must enter a number!", vbExclamation, "NUMBER GREATER THAN 0 REQUIRED"
            Target.Cells(1, 1) = ""
            GoTo NextStep
        ElseIf Target.Cells(1, 1) < 0 Then
            MsgBox "You're getting paid that badly!?", vbExclamation, "NUMBER GREATER THAN 0 REQUIRED"
            Target.Cells(1, 1) = ""
            GoTo NextStep
        End If
    End If
End If

'make sure the fringe benefit types in P23:P62 are appropriate
If Target.Column = 16 Then
    If Target.Row >= 23 And Target.Row <= 62 Then
        If Target.Cells(1, 1) <> "" And Target.Cells(1, 1) <> "F" And Target.Cells(1, 1) <> "U" And Target.Cells(1, 1) <> "A" And Target.Cells(1, 1) <> "P" And Target.Cells(1, 1) <> "T" And Target.Cells(1, 1) <> "R" And Target.Cells(1, 1) <> "S" And Target.Cells(1, 1) <> "W" And Target.Cells(1, 1) <> "f" And Target.Cells(1, 1) <> "a" And Target.Cells(1, 1) <> "p" And Target.Cells(1, 1) <> "t" And Target.Cells(1, 1) <> "r" And Target.Cells(1, 1) <> "u" And Target.Cells(1, 1) <> "s" And Target.Cells(1, 1) <> "w" Then
            MsgBox "You must enter a fringe benefit code from the list above!", vbExclamation, "'F','A','P','T','R','S', OR 'W' REQUIRED"
            Target.Cells(1, 1) = ""
            GoTo NextStep
        End If
    End If
End If
 
    
    If Target.Row >= 23 And Target.Row <= 62 Then
'make sure the project duration in L10 is a positive number
If Target.Column = 12 And Target.Row = 10 Then
        If Not IsNumeric(Target.Cells(1, 1)) Then
            MsgBox "You must enter a number!", vbExclamation, "NUMBER GREATER THAN 0 REQUIRED"
            Target.Cells(1, 1) = ""
            GoTo NextStep
        ElseIf Target.Cells(1, 1) < 0 Then
            MsgBox "Are you sure the project is that short?", vbExclamation, "NUMBER GREATER THAN 0 REQUIRED"
            Target.Cells(1, 1) = ""
            GoTo NextStep
        End If
End If
NextStep: ' added line
    Dim r       As Range
    Dim cell    As Range



    Set r = Intersect(Target, Rows("23:62"), Union(Columns("P"), Columns("R")))
    If r Is Nothing Then Exit Sub

    For Each cell In r
        With cell
            If .Column = Columns("P").Column Then
                If .Value = "U" And Not IsEmpty(Cells(.Row, "R").Value) Then
                    With Cells(.Row, "R")
                        .ClearContents
                        .Select
                        MsgBox "Must be blank.", vbOKOnly
                    End With
                End If

            Else    ' it's in column R
                If Cells(.Row, "P").Value = "U" And Not IsEmpty(.Value) Then
                    .ClearContents
                    .Select
                    MsgBox "Must be blank.", vbOKOnly
                End If
            End If
        End With
    Next cell
End Sub

Now its not doing anything.

**shg** · 08-20-2008, 06:47 PM

You left off one of your End If statements. There should be (another) one above NextStep:

**emad1001** · 08-21-2008, 03:29 PM

Okay this is what I have:


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'this is all for validation of user input on the details sheet

'make sure a date has been input in E11 and E12
If Target.Cells(1, 1).Address(0, 0) = "E11" Then
    If Target.Cells(1, 1) <> "" And Not IsDate(Target.Cells(1, 1)) Then
        MsgBox "You must input a date!", vbExclamation, "DATE REQUIRED"
        Target.Cells(1, 1) = ""
        GoTo NextStep
    End If
End If
If Target.Cells(1, 1).Address(0, 0) = "E12" Then
    If Target.Cells(1, 1) <> "" And Not IsDate(Target.Cells(1, 1)) Then
        MsgBox "You must input a valid date!", vbExclamation, "DATE REQUIRED"
        Target.Cells(1, 1) = ""
        GoTo NextStep
    End If
End If

'make sure enddate>=startdate
If Sheets("Salary Detail").Range("enddate") <> "" And Sheets("Salary Detail").Range("startdate") <> "" Then
    If Sheets("Salary Detail").Range("enddate") < Sheets("Salary Detail").Range("startdate") Then
        MsgBox "We've heard of short projects before, but this one really takes the cake!", vbExclamation, "END DATE BEFORE START DATE"
        Sheets("Salary Detail").Range("enddate") = ""
        GoTo NextStep
    End If
End If
    
'make sure enddate<=startdate + 366
If Sheets("Salary Detail").Range("enddate") <> "" And Sheets("Salary Detail").Range("startdate") <> "" Then
    If Sheets("Salary Detail").Range("enddate") > Sheets("Salary Detail").Range("startdate") + 366 Then
        MsgBox "This project has a very long initial year!", vbExclamation, "END DATE BEFORE START DATE"
        Sheets("Salary Detail").Range("enddate") = ""
        GoTo NextStep
    End If
End If

'make sure FTE's in C23:C62  and the levels of effort in H23:H62 are in the range 0-1
If Target.Column = 3 Or Target.Column = 8 Then
    If Target.Row >= 23 And Target.Row <= 62 Then
        If Not IsNumeric(Target.Cells(1, 1)) Then
            MsgBox "You must enter a number between 0 and 1!", vbExclamation, "NUMBER BETWEEN 0 AND 1 REQUIRED"
            Target.Cells(1, 1) = ""
            GoTo NextStep
        ElseIf Target.Cells(1, 1) < 0 Or Target.Cells(1, 1) > 1 Then
            MsgBox "You must enter a number between 0 and 1!", vbExclamation, "NUMBER BETWEEN 0 AND 1 REQUIRED"
            Target.Cells(1, 1) = ""
            GoTo NextStep
        End If
    End If
End If

'make sure the salaries in E23:E62 are positive numbers
If Target.Column = 5 Then
    If Target.Row >= 23 And Target.Row <= 62 Then
        If Not IsNumeric(Target.Cells(1, 1)) Then
            MsgBox "You must enter a number!", vbExclamation, "NUMBER GREATER THAN 0 REQUIRED"
            Target.Cells(1, 1) = ""
            GoTo NextStep
        ElseIf Target.Cells(1, 1) < 0 Then
            MsgBox "You're getting paid that badly!", vbExclamation, "NUMBER GREATER THAN 0 REQUIRED"
            Target.Cells(1, 1) = ""
            GoTo NextStep
        End If
    End If
End If

'make sure the fringe benefit types in P23:P62 are appropriate
If Target.Column = 16 Then
    If Target.Row >= 23 And Target.Row <= 62 Then
        If Target.Cells(1, 1) <> "" And Target.Cells(1, 1) <> "F" And Target.Cells(1, 1) <> "U" And Target.Cells(1, 1) <> "A" And Target.Cells(1, 1) <> "P" And Target.Cells(1, 1) <> "T" And Target.Cells(1, 1) <> "R" And Target.Cells(1, 1) <> "S" And Target.Cells(1, 1) <> "W" And Target.Cells(1, 1) <> "f" And Target.Cells(1, 1) <> "a" And Target.Cells(1, 1) <> "p" And Target.Cells(1, 1) <> "t" And Target.Cells(1, 1) <> "r" And Target.Cells(1, 1) <> "u" And Target.Cells(1, 1) <> "s" And Target.Cells(1, 1) <> "w" Then
            MsgBox "You must enter a fringe benefit code from the list above!", vbExclamation, "'F','A','P','T','R','S', OR 'W' REQUIRED"
            Target.Cells(1, 1) = ""
            GoTo NextStep
        End If
    End If
End If

'make sure the project duration in L10 is a positive number
If Target.Column = 12 And Target.Row = 10 Then
        If Not IsNumeric(Target.Cells(1, 1)) Then
            MsgBox "You must enter a number!", vbExclamation, "NUMBER GREATER THAN 0 REQUIRED"
            Target.Cells(1, 1) = ""
            GoTo NextStep
        ElseIf Target.Cells(1, 1) < 0 Then
            MsgBox "Are you sure the project is that short?", vbExclamation, "NUMBER GREATER THAN 0 REQUIRED"
            Target.Cells(1, 1) = ""
            GoTo NextStep
        End If
End If

End Sub
NextStep: ' added line
    Dim r       As Range
    Dim cell    As Range

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim r       As Range
    Dim cell    As Range

    Set r = Intersect(Target, Rows("23:62"), Union(Columns("P"), Columns("R")))
    If r Is Nothing Then Exit Sub

    For Each cell In r
        With cell
            If .Column = Columns("P").Column Then
                If .Value = "U" And Not IsEmpty(Cells(.Row, "R").Value) Then
                    With Cells(.Row, "R")
                        .ClearContents
                        .Select
                        MsgBox "Must be blank.", vbOKOnly
                    End With
                End If

            Else    ' it's in column R
                If Cells(.Row, "P").Value = "U" And Not IsEmpty(.Value) Then
                    .ClearContents
                    .Select
                    MsgBox "Must be blank.", vbOKOnly
                End If
            End If
        End With
    Next cell
End Sub

Now it is still giving me the Ambigious name detected: Worksheet_Change
How would I change that?
Thanks!

**shg** · 08-21-2008, 03:47 PM

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    'this is all for validation of user input on the details sheet

    'make sure a date has been input in E11 and E12
    If Target.Cells(1, 1).Address(0, 0) = "E11" Then
        If Target.Cells(1, 1) <> "" And Not IsDate(Target.Cells(1, 1)) Then
            MsgBox "You must input a date!", vbExclamation, "DATE REQUIRED"
            Target.Cells(1, 1) = ""
            GoTo NextStep
        End If
    End If
    
    If Target.Cells(1, 1).Address(0, 0) = "E12" Then
        If Target.Cells(1, 1) <> "" And Not IsDate(Target.Cells(1, 1)) Then
            MsgBox "You must input a valid date!", vbExclamation, "DATE REQUIRED"
            Target.Cells(1, 1) = ""
            GoTo NextStep
        End If
    End If

    'make sure enddate>=startdate
    If Sheets("Salary Detail").Range("enddate") <> "" And Sheets("Salary Detail").Range("startdate") <> "" Then
        If Sheets("Salary Detail").Range("enddate") < Sheets("Salary Detail").Range("startdate") Then
            MsgBox "We've heard of short projects before, but this one really takes the cake!", vbExclamation, "END DATE BEFORE START DATE"
            Sheets("Salary Detail").Range("enddate") = ""
            GoTo NextStep
        End If
    End If

    'make sure enddate<=startdate + 366
    If Sheets("Salary Detail").Range("enddate") <> "" And Sheets("Salary Detail").Range("startdate") <> "" Then
        If Sheets("Salary Detail").Range("enddate") > Sheets("Salary Detail").Range("startdate") + 366 Then
            MsgBox "This project has a very long initial year!", vbExclamation, "END DATE BEFORE START DATE"
            Sheets("Salary Detail").Range("enddate") = ""
            GoTo NextStep
        End If
    End If

    'make sure FTE's in C23:C62  and the levels of effort in H23:H62 are in the range 0-1
    If Target.Column = 3 Or Target.Column = 8 Then
        If Target.Row >= 23 And Target.Row <= 62 Then
            If Not IsNumeric(Target.Cells(1, 1)) Then
                MsgBox "You must enter a number between 0 and 1!", vbExclamation, "NUMBER BETWEEN 0 AND 1 REQUIRED"
                Target.Cells(1, 1) = ""
                GoTo NextStep
            ElseIf Target.Cells(1, 1) < 0 Or Target.Cells(1, 1) > 1 Then
                MsgBox "You must enter a number between 0 and 1!", vbExclamation, "NUMBER BETWEEN 0 AND 1 REQUIRED"
                Target.Cells(1, 1) = ""
                GoTo NextStep
            End If
        End If
    End If

    'make sure the salaries in E23:E62 are positive numbers
    If Target.Column = 5 Then
        If Target.Row >= 23 And Target.Row <= 62 Then
            If Not IsNumeric(Target.Cells(1, 1)) Then
                MsgBox "You must enter a number!", vbExclamation, "NUMBER GREATER THAN 0 REQUIRED"
                Target.Cells(1, 1) = ""
                GoTo NextStep
            ElseIf Target.Cells(1, 1) < 0 Then
                MsgBox "You're getting paid that badly!", vbExclamation, "NUMBER GREATER THAN 0 REQUIRED"
                Target.Cells(1, 1) = ""
                GoTo NextStep
            End If
        End If
    End If

    'make sure the fringe benefit types in P23:P62 are appropriate
    If Target.Column = 16 Then
        If Target.Row >= 23 And Target.Row <= 62 Then
            If Target.Cells(1, 1) <> "" And Target.Cells(1, 1) <> "F" And Target.Cells(1, 1) <> "U" And Target.Cells(1, 1) <> "A" And Target.Cells(1, 1) <> "P" And Target.Cells(1, 1) <> "T" And Target.Cells(1, 1) <> "R" And Target.Cells(1, 1) <> "S" And Target.Cells(1, 1) <> "W" And Target.Cells(1, 1) <> "f" And Target.Cells(1, 1) <> "a" And Target.Cells(1, 1) <> "p" And Target.Cells(1, 1) <> "t" And Target.Cells(1, 1) <> "r" And Target.Cells(1, 1) <> "u" And Target.Cells(1, 1) <> "s" And Target.Cells(1, 1) <> "w" Then
                MsgBox "You must enter a fringe benefit code from the list above!", vbExclamation, "'F','A','P','T','R','S', OR 'W' REQUIRED"
                Target.Cells(1, 1) = ""
                GoTo NextStep
            End If
        End If
    End If

    'make sure the project duration in L10 is a positive number
    If Target.Column = 12 And Target.Row = 10 Then
        If Not IsNumeric(Target.Cells(1, 1)) Then
            MsgBox "You must enter a number!", vbExclamation, "NUMBER GREATER THAN 0 REQUIRED"
            Target.Cells(1, 1) = ""
            GoTo NextStep
        ElseIf Target.Cells(1, 1) < 0 Then
            MsgBox "Are you sure the project is that short?", vbExclamation, "NUMBER GREATER THAN 0 REQUIRED"
            Target.Cells(1, 1) = ""
            GoTo NextStep
        End If
    End If

NextStep:

    Dim r       As Range
    Dim cell    As Range

    Set r = Intersect(Target, Rows("23:62"), Union(Columns("P"), Columns("R")))
    If r Is Nothing Then Exit Sub

    For Each cell In r
        With cell
            If .Column = Columns("P").Column Then
                If .Value = "U" And Not IsEmpty(Cells(.Row, "R").Value) Then
                    With Cells(.Row, "R")
                        .ClearContents
                        .Select
                        MsgBox "Must be blank.", vbOKOnly
                    End With
                End If

            Else    ' it's in column R
                If Cells(.Row, "P").Value = "U" And Not IsEmpty(.Value) Then
                    .ClearContents
                    .Select
                    MsgBox "Must be blank.", vbOKOnly
                End If
            End If
        End With
    Next cell
End Sub