Make a range of Cells Mandatory rather than writing separate code for each cell

[Moseth]

HI,
If Application.Sheets("Supplier Checklist").Range("C2").Cells = "" Then
Cancel = True
MsgBox "Please fill in the mandatory cells"
End If
For example if I want to make cells C2 to C21 all mandatory, is there an easier way to do this other tahn writing separate lines for each cell, how about if I wanted to make C2;C21 then D35-D49 mandatory. is there an easier way to code?
at the moment I am writing one line of code for one cell. Any easier way?

 

[Joe4]

You can use the COUNTA function to count the number of cells with entries to see if they all have values, i.e.

If Application.WorksheetFunction.CountA(Range("C2:C21")) < 20 Then
[COLOR=#333333]    Cancel = True[/COLOR]
[COLOR=#333333]    MsgBox "Please fill in the mandatory cells"[/COLOR]
[COLOR=#333333]End If[/COLOR]

 

[Fluff]

How about

Sub MustFill()
   If Application.CountA(Sheets("Supplier Checklist").Range("C2:C21,D35:D49")) < 35 Then
      MsgBox "Please fill in the mandatory cells"
   End If
End Sub

 

[Moseth]

Thankyou