VBA code to find blanks cell & to see if values are greater than allowed.

[danbates]

Hi,

Please can someone help me?

I am trying to put together a code to do the following:
1. Checks for any blank cells in my range
2. Then checks the values of them cells against the cells with the values that shouldn't be exceeded without a reason.

Here is my code so far:

Sub CheckBlankCells()

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


    Dim newrange As Range


Set newrange = Range(Range("C" & Selection.Row), Range("H" & Selection.Row))


For Each cell In newrange
    If IsEmpty(cell) = True Then
    aaaaaUnlockandDisable
            str = str & Cells(4, cell.Column).Value & vbCrLf
            cell.Interior.Color = RGB(255, 0, 0)
        bIsEmpty = True
    End If
Next cell

If bIsEmpty = True Then
MsgBox "Please complete the following before entering your details:  " & vbNewLine & vbNewLine & str, vbInformation, "Palletiser Operator"
For Each cell In newrange
cell.Interior.Color = RGB(255, 255, 255)
Next cell
    aaaaaLockandProtect


Range("C" & Selection.Row).Select


    End If

End Sub

The first part works fine, it's really the second part I am struggling with but I've added the first part because I would like it all to work in 1 routine.

What I need is for:

Selection Row D value to be checked against D6 value
Selection Row E value to be checked against E6 value
Selection Row F value to be checked against F6 value
Selection Row G value to be checked against G6 value
Selection Row H value to be checked against H6 value

Row 6 cell values will not change.

I would like it all to work the same as the blank cells so if there is more than 1 exceeds its limit, they will all be captured in 1 message box.

But if there is a blank cell I don't want the second part triggering.

I hope all this makes sense but if not just let me know.

Thanks
Dan