I tried to insert the following code into an Excel macro

If Range("m1") = "" Then
MsgBox ("Number of Units is blank")
End If

I am doing something wrong, because I get the msgbox whether cell m1 is empty or not. Can anyone help me with the correct syntax?

That should work as long as you don't have a space in the cell...
Try this:
If Trim(Range("m1").Text) = "" Then
MsgBox ("Number of Units is blank")
End If