Need Help Please...

[mloucel]

Hello AGAIN..
I need to check some cells for data on them..

Range r2 to r1500 = User will enter his/her name when this happens..

Check cells F2:f1500, g2:g1500, h2:h1500 if they are not empty
if ANY of the cells is empty
Display a message saying "Data is missing in XYZ please correct"

So I created this

Private Sub Worksheet_Change(ByVal Target As Range)


    Dim myRange As Range, otherRange As Range, rngCell As Range


    Application.EnableEvents = False
    Set otherRange = Intersect(Target, Range("G2:G1500, H2:H1500, N2:N1500, R2:R1500, T2:U1500, AB2:AB1500"))
    Set myRange = Intersect(Target, Range("AG2:AG1500, AI2:AI1500, AK2:AK1500"))
    Set TestNRange = Intersect(Target, Range("F2:F1500, G2:G1500, H2:H1500"))
[COLOR=#ff0000][B] '   '
 ' THIS CODE DOES NOT WORK 
'[/B][/COLOR]
[COLOR=#ff0000][B]    If Not TestNRange Is Nothing Then[/B][/COLOR]
[COLOR=#ff0000][B]        For Each Value2Check In TestNRange[/B][/COLOR]
[COLOR=#ff0000][B]            If Len(Value2Check) = 0 Then[/B][/COLOR]
[COLOR=#ff0000][B]                    MsgBox "DATA is missing for either:" & vbNewLine _[/B][/COLOR]
[COLOR=#ff0000][B]                    & "Referral Date Thru Referral by Doctor" & vbNewLine & " " & vbNewLine & "Please Correct"[/B][/COLOR]
[COLOR=#ff0000][B]            End If[/B][/COLOR]
[COLOR=#ff0000][B]        Next Value2Check[/B][/COLOR]
[COLOR=#ff0000][B]    End If[/B][/COLOR]
[COLOR=#ff0000][B]'   '[/B][/COLOR]
    If Not otherRange Is Nothing Then
        For Each rngCell In otherRange
            rngCell.Value = UCase(rngCell.Value)
        Next rngCell
    End If
    
    If Not myRange Is Nothing Then
        For Each rngCell In myRange
            If Len(rngCell) Then
                rngCell.Value = UCase(rngCell.Value)
                If Len(rngCell.Offset(, -1)) = 0 Then rngCell.Offset(, -1).Value = Date
            Else
                rngCell.Offset(, -1).ClearContents
            End If
        Next rngCell
    End If
    Application.EnableEvents = True
    
End Sub

TestNRange is always nothing and I simply can't figure out why..

Sorry I'm very new in VB ....

 

[mloucel]

Hello..

Sorry anyone can kindly get me to the right direction.. Please..