Macro to show a message box if even one cell in a row is blank

[patsdavixen]

Hi,

I been working on a macro that highlights all blank cells in a row till the end of a variable range (the number of rows are variable but the columns are limited from A:AI) to be highlighted in purple background using conditional formatting.
What I am struggling with is that I need a macro to show a message box if even one cell in the variable range from A2:AI2 (the number of rows are variable but the columns are limited from A:AI) is blank and is highlighted in the purple colour conditional formatting.
If there are no blank cells in the row, I want it to state "Completed" and if even one cell is blank in the range, it should show a message box stating "Please fill all blank cells highlighted in purple"
The macro should loop until the user updates all blank cells.
Here is the code so far but I can't seem to get the IF function for the macro to loop to work.. It runs all the way to "Completed" even if there are blank cells. With the Exit sub included it doesn't even run till "Completed". Could someone please help me?

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 "Completed"
    End If


End Sub

This is very urgent so I would appreciate your help.

Thanks,
Pat

 

[kevin lazell]

hi pat
not tested but try
changing if rng = 0 then
to if j = 0 then
cheers
kevin

 

[patsdavixen]

Hi Kevin,

Thanks for replying.
I tried changing rng to j and I took out the Exit Sub but it still shows the "Completed" message even when there are blank cells present in the range.

 

[Jerry Sullivan]

Pat, Displaying the MsgBox pauses the execution of the macro, but it will also prevent the user from making any changes to the sheet (filling in the blanks).

Perhaps use conditional formatting to display the blanks and have your macro simply...
Check for blanks, advise user that there are X blanks to be completed, then exit.

 

[rjwebgraphix]

Untested, but if I understand the need correctly, this may work.

Sub chk4blanks()

    Dim i As Long, j As Long, lastrow As Long
    Dim rng As Range
    Dim BlankCnt As Long
    
    lastrow = Cells(Rows.count, "A").End(xlUp).row
    
    For i = 2 To lastrow
        BlankCnt = 0
        For j = 1 To 35
            If IsEmpty(Cells(i, j)) Then
                BlankCnt = BlankCnt + 1
            End If
        Next j
        If BlankCnt <> 0 Then
            MsgBox "There are [" & BlankCnt & "] blank cells in [Row " & i & "]. Please fill all blank cells highlighted in purple"
            Exit Sub
        End If
    Next i
    
    MsgBox "Completed"
    

End Sub

 

[patsdavixen]

To begin with, this forum is awesome

 

[patsdavixen]

To begin with, this forum is awesome
Thank you for all the help.
Kevin, your comment got me thinking and I came up with this code

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
    If j = 0 Then
    MsgBox "Retrieval process completed"
     Else
     End If
End Sub

And jrwebgraphix, your code works perfectly... Thanks again everyone