For Each Statement not working as expected
 

I have the following syntax using a For Each statement to check column c and
if this contains a value which is NOT in bold text, the code checks columns
K:N and if these cells are empty the user receives an error message via a
msgbox. However if I deliberately create known errors on the sheet the
syntax is finding the first occurance only and ignoring the others. How can
I fix please, the VBA is below

Thanks
Jacqui

Sub Qualifiers_Check()

Set wks = ActiveWorkbook.Worksheets("Part B - Coding Details")

With wks

Set myRng = .Range("c20", .Cells(.Rows.Count, "c").End(xlUp))

Set myRng = myRng.Resize(myRng.Count - 1)

For Each myCell In myRng.Cells
If IsEmpty(myCell.Value) = False And Not myCell.Font.Bold Then
Set myRngToCheck = .Cells(myCell.Row, "k").Resize(1, 4)
If Application.CountA(myRngToCheck) < myRngToCheck.Cells.Count
Then
Beep
MsgBox "You have not supplied all the relevant information
for this Segment type in Row " _
& myCell.Row & " on the Coding Details Sheet - PLEASE ENTER
ALL DETAILS"

End If
End If
Next myCell

End With

End Sub

For Each Statement not working as expected
 

Hi Jacqui

in line -Set myRngToCheck = .Cells(myCell.Row, "k").Resize(1, 4)- missing
object

Try -Set myRngToCheck = ActiveSheet.Cells(myCell.Row, "k").Resize(1, 4)-

Carlos
"Jacqui" wrote in message
...
I have the following syntax using a For Each statement to check column c
and
if this contains a value which is NOT in bold text, the code checks
columns
K:N and if these cells are empty the user receives an error message via a
msgbox. However if I deliberately create known errors on the sheet the
syntax is finding the first occurance only and ignoring the others. How
can
I fix please, the VBA is below

Thanks
Jacqui

Sub Qualifiers_Check()

Set wks = ActiveWorkbook.Worksheets("Part B - Coding Details")

With wks

Set myRng = .Range("c20", .Cells(.Rows.Count, "c").End(xlUp))

Set myRng = myRng.Resize(myRng.Count - 1)

For Each myCell In myRng.Cells
If IsEmpty(myCell.Value) = False And Not myCell.Font.Bold Then
Set myRngToCheck = .Cells(myCell.Row, "k").Resize(1, 4)
If Application.CountA(myRngToCheck) < myRngToCheck.Cells.Count
Then
Beep
MsgBox "You have not supplied all the relevant information
for this Segment type in Row " _
& myCell.Row & " on the Coding Details Sheet - PLEASE
ENTER
ALL DETAILS"

End If
End If
Next myCell

End With

End Sub