Thanks, Palmetto. Once again your code works perfectly 
I only wanted to make one change to it. The macro runs all the way to "Completed" even if there are blank cells present in the variable range.
Would it be possible to make it stop if there are blank cells present and only show the "Completed" message box if all blank cells are filled in.
I do not know how to make the if function work in the code.. here's what I tried:
Sub chk4blanks()
Dim i As Long, j As Long, lastrow As Long
Dim rng As Range
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 2 To lastrow
Set rng = Range(Cells(i, 1), Cells(i, 35))
j = Application.WorksheetFunction.CountBlank(rng)
If j > 0 Then
MsgBox "There are [" & j & "] blank cells in [Row " & i & "]. Please fill all blank cells highlighted in purple"
End If
Next i
Exit Sub
If rng = 0 Then
MsgBox "Retrieval process completed"
End If
End Sub
Pat
Bookmarks