Rammed up on empty cell code

[Castyel]

I running a report that is dymaic and the number of rows change and I need to make mandatory cells. Now I may just be tired but I cant seem to get it to see there is something in the cell and it always comes up with blank when it finds a line with a 1. plus can this work fork more then 2 cells code bellow. range is only 3 row long for testing

Sub manditory1()
For Each i In Range("S47:S50")
If i = 1 Then
For Each cell In Sheets("Multi").Range("D" & ActiveCell.Row, "F" & ActiveCell.Row)
If IsEmpty(ActiveCell) Then
break = x
Cancel = True
Exit For
End If
Next cell

End If
Next i


If break = x Then
MsgBox "Mandatory field not completed"

Else
End If

End Sub

 

[Norie]

Do you want to check every row where 1 is in a particular column?

 

[Castyel]

yes every time there is a 1 in column S I need it to check that row to see if those cells have been filled in

 

[Norie]

The first thing you need is a loop through all the rows with data.

Are there are any columns which which always have data on each row?

If column A always had data you would have something like this.

Sub Mandatory()
'D,F,G,H,J,M,N,P & Q
Dim rng As Range
Dim cl As Range
Dim I As Long
Dim LastRow As Long

    LastRow = Range("A" & Rows.Count).End(xlUp).Row

    For I = 2 To LastRow    ' start at 2 assuming there's a header

        ' check if column S has a 1 in it.
        If Range("S" & I).Value = 1 Then

            ' check cells in columns D,F,G,H,J,M,N,P and Q
            Set rng = Intersect(Rows(I), Range("D:D, F:H, J:J, M:N, P:Q"))

            For Each cl In rng.Cells
                If cl.Value = "" Then
                    Debug.Print "Missing entry in column " & Split(cl.Address, "$")(1) & ", row " & cl.Row
                    cl.Interior.ColorIndex = 3
                End If
            Next cl

        End If

    Next I
    
End Sub

 

[Castyel]

OMG Thank you thank you thank you. I maybe able to get 2 hours sleep tonight before I have to go in.

One question if I was putting this in as a check before the report was sent by linking it to a send macro and I put that link after the next I would it still send even it there was blank cells?

 

[Norie]

Sorry, since this is a duplicate thread and you are getting help in the other thread I don't think I should contribute anymore.