You should really use Option Explicit and declare
all your variables, not just some of them.
Have a look at
this page.
Give this macro a try
Code:
Public Sub HighLightEmptyCells()
Dim myRange As Range
Dim myCell As Range
Dim lastrow As Long
Dim anyBlanks As Boolean
With ActiveSheet
lastrow = .Cells.Find("*", .Cells(1, 1), , , xlByRows, xlPrevious).Row
Set myRange = Application.Union(.Range("B3:D" & lastrow), .Range("F3:F" & lastrow))
anyBlanks = False
For Each myCell In myRange
If myCell.Value = "" Then
myCell.Interior.Color = vbRed
anyBlanks = True
Else
myCell.Interior.Color = xlNone
End If
Next myCell
If anyBlanks = True Then
MsgBox " Please fill up Mandatory Fields Highlighted in Red"
Exit Sub
Else
MsgBox "OK -- Ready to draft email"
' Application.Dialogs(xlDialogSendMail).Show "xyz@abc.com"
End If
End With
End Sub