Alter code to show more than 1 blank cell heading in message box

[danbates]

Hi,

I have the following code I would like altering so if there is more than 1 blank cell in the range it states which ones via the cell headings in the message box.

Sub IsEmptyRange()Dim cell As Range
Dim bIsEmpty As Boolean


bIsEmpty = False


For Each cell In Range("B7:D7,F7")
    If IsEmpty(cell) = True Then
        'An empty cell was found. Exit loop
        bIsEmpty = True
        Exit For
    End If
Next cell


If bIsEmpty = True Then


    MsgBox "There are empty cells in your range  " & Cells(6, cell.Column).Value


Else
    
    MsgBox "All cells have values!"


End If
End Sub

Thanks

Dan

 

[steve the fish]

What are the cell headings?

 

[danbates]

They are just random headings at the moment on row 6

Order = Column B
Password = Column C
Product = Column D
QTY = Column F

Thanks

 

[danbates]

Hi,

I've managed to get it to work how I would like but I think the code is a little messy.

Sub MyCellCheck()

    Dim cell As Range
    Dim str As String
    Dim bIsEmpty As Boolean


    
For Each cell In Range("B7:D7,F7")
    If IsEmpty(cell) = True Then
            str = str & Cells(6, cell.Column).Value & vbCrLf
            cell.Interior.Color = RGB(255, 0, 0)
        bIsEmpty = True
        'Exit For
    End If
Next cell
    
    
If bIsEmpty = True Then


    MsgBox str


Else
    
    MsgBox "All cells have values!"
    
    End If
    
[COLOR=#ff0000]For Each cell In Range("B7:D7,F7")
    
    If IsEmpty(cell) = True Then
            str = str & Cells(6, cell.Column).Value & vbCrLf
            cell.Interior.Color = RGB(255, 255, 255)
        bIsEmpty = True
        'Exit For
    End If
  Next cell[/COLOR]
  
End Sub

I am sure there is a easier way so please could someone help me clean the last part of the code (in red) where I have changed the colour of the empty cells from red to white?

Thanks

Dan