Checking just a range not the whole row

[mloucel]

I need to check ONLY a certain range of columns to be empty and display a message to the End user so he can correct the problem

this is the code I have so far, it works but not the way I want..

    Set TestInRange = Intersect(Target, Range("F2:F1500, G2:G1500, H2:H1500, I2:I1500, I2:I1500, J2:J1500, K2:K1500, L2:L1500, M2:M1500, N2:N1500, O2:O1500, P2:P1500"))
   
    If Len(Cells(Target.Row, "R")) <> 0 Then  ' IF the user is in R is R empty or does it have Data ?
        If TestInRange Is Nothing Then            ' IF R has Data then, is there any cell in my TestInRange that is empty ?
            WhereIAm = Target.Row
            MsgBox "THERE ARE IMPORTANT CELLS LEFT EMPTY " & vbCrLf & "" _
            & vbCrLf & "YOU MUST Fill these cells:" _
            & vbCrLf & "" _
            & vbCrLf & "Patient Last or First Name,DOB, Chart Number, Phone Number," _
            & vbCrLf & "IPA, Health Plan, Diagnosis, Referred to or Referral by Doctor," _
            & vbCrLf & "(ONE OR MORE THAN ONE) is/are Empty." _
            & vbCrLf & " " _
            & vbCrLf & "Please go back and re-enter the values...", _
            vbCritical, "Validation NOT PASSED..."
           
           ' Forcing the User to go to DATE CELL
            ActiveCell.Offset(-1, -12).Select        
            '
        End If
   End If

PROBLEM:

Not only checks for the specific Columns but for the WHOLE Row the user is at that moment, so if there are empty cells after P the message displays again, which is not what I want, I'm fairly new and I have tried some other things to no avail.. I know my logic is somewhat correct but I just don't know enough code to figure out what instruction should I use to get my objective.

Please Gurus can you lend a hand..

Thanks.

 

[RCBricker]

I need to check ONLY a certain range of columns to be empty and display a message to the End user so he can correct the problem

this is the code I have so far, it works but not the way I want..

    Set TestInRange = Intersect(Target, Range("F2:F1500, G2:G1500, H2:H1500, I2:I1500, I2:I1500, J2:J1500, K2:K1500, L2:L1500, M2:M1500, N2:N1500, O2:O1500, P2:P1500"))
   
    If Len(Cells(Target.Row, "R")) <> 0 Then  ' IF the user is in R is R empty or does it have Data ?
        If TestInRange Is Nothing Then            ' IF R has Data then, is there any cell in my TestInRange that is empty ?
            WhereIAm = Target.Row
            MsgBox "THERE ARE IMPORTANT CELLS LEFT EMPTY " & vbCrLf & "" _
            & vbCrLf & "YOU MUST Fill these cells:" _
            & vbCrLf & "" _
            & vbCrLf & "Patient Last or First Name,DOB, Chart Number, Phone Number," _
            & vbCrLf & "IPA, Health Plan, Diagnosis, Referred to or Referral by Doctor," _
            & vbCrLf & "(ONE OR MORE THAN ONE) is/are Empty." _
            & vbCrLf & " " _
            & vbCrLf & "Please go back and re-enter the values...", _
            vbCritical, "Validation NOT PASSED..."
           
           ' Forcing the User to go to DATE CELL
            ActiveCell.Offset(-1, -12).Select        
            '
        End If
   End If

PROBLEM:

Not only checks for the specific Columns but for the WHOLE Row the user is at that moment, so if there are empty cells after P the message displays again, which is not what I want, I'm fairly new and I have tried some other things to no avail.. I know my logic is somewhat correct but I just don't know enough code to figure out what instruction should I use to get my objective.

Please Gurus can you lend a hand..

Thanks.

something like this might work

Dim rng As Range
Dim lngF As Long, lngCOL As Long
    
If Len(Cells(Target.Row, "R")) <> 0 Then  ' IF the user is in R is R empty or does it have Data ?
    Set rng = Range(.Cells(2, 6), .Cells(1500, 16))
    lngCOL = rng.Find("").Column
    If lngCOL > 0 Then          ' IF R has Data then, is there any cell in my TestInRange that is empty ?
        WhereIAm = Target.Row
            MsgBox "THERE ARE IMPORTANT CELLS LEFT EMPTY " & vbCrLf & "" _
            & vbCrLf & "YOU MUST Fill these cells:" _
            & vbCrLf & "" _
            & vbCrLf & "Patient Last or First Name,DOB, Chart Number, Phone Number," _
            & vbCrLf & "IPA, Health Plan, Diagnosis, Referred to or Referral by Doctor," _
            & vbCrLf & "(ONE OR MORE THAN ONE) is/are Empty." _
            & vbCrLf & " " _
            & vbCrLf & "Please go back and re-enter the values...", _
            vbCritical, "Validation NOT PASSED..."
   
        ' Forcing the User to go to DATE CELL
         ActiveCell.Offset(-1, -12).Select
    End If
End If

 

[mloucel]

Mr. RCBricker

I'm very grateful, I am really new to vba programming, there are many thousands of tricks I need to learn,
I usually go line by line trying to understand what the code does and how can I apply that in the future of mix that with something else to create a new piece.
I was a bit confused here:
Set rng = Range(cells(2, 6), cells(1500,16))
(I had to take the (.) out of the formula it was giving me an error:
Compile Error:
Invalid or unqualified reference

But I understood what the next line is doing (lngCOL = rng.Find("").Column) I have never seen it used like that, but I knew it looks for the first empty cell within a column so the line in question should be position me in Column R somehow, those two lines are a bit tricky (FOR ME) and they are way beyond what I know, but they are a challenge for me to learn
I am going to investigate a bit further about how the Range and Cell commands work, maybe that would make my life easier.

I appreciate your kind answer and help and I am forever thankful.

You are a real master GURU in my eyes.

Sincerely;

Maurice.

 

[mloucel]

I just placed that into the production worksheet and when it checks for empty cells within my range (Fn to Pn) where n = any cell number from 2 to 1500, this is my test range " Set TestInRange = Intersect(Range("F2:P1500"), Target) "

I am guessing here.... But I believe is checking cols A thru E ?? because those are the only ones are empty allways, they just keep a color switch i use, I might be wrong??
Do you mind giving me an insight on this code Set rng = Range(cells(2, 6), cells(1500,16)), it seems like my issue is here but I simply do not understand the line..

Thanks..