View Single Post
 
Old 08-13-2015, 09:55 AM
NoSparks NoSparks is offline Windows 7 64bit Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 842
NoSparks is a glorious beacon of lightNoSparks is a glorious beacon of lightNoSparks is a glorious beacon of lightNoSparks is a glorious beacon of lightNoSparks is a glorious beacon of light
Default

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
Reply With Quote