I have a simple code based on an if statement depending on cell value range. I am making a form that is routed if this particular cell is that is equal to 0 "O18" that no is blanks. Basically the value is based on countif validating other cells are filled out on a form. If the they are all filled out, the countif cell will = to 0 and the form will route to it's destination.
'Macro Created 07/1/2004
'Scroll up to beginning of sheet
ActiveWindow.LargeScroll Up:=5
Range("A1").Select
Dim intResponse As Integer
MsgBox ("Your Document will be sent to the Appropriate Department. A Copy of This form is in your Sent Items Mailbox.")
'Cell must = 0 to send to address
If Range("018") = 0 Then "This is where the error stops"
'Validate crticial information
ActiveWorkbook.HasRoutingSlip = True
With ActiveWorkbook.RoutingSlip
.Recipients = "em***@anonymous.com"
.Subject = "Adverse Event.xlt"
.Message = "Adverse Event.xlt"
.Delivery = xlAllAtOnce
.ReturnWhenDone = False
.TrackStatus = True
Range("e5,j5,e6,d11,j9,j11,d28,I28,d29,d30,i29,k29 ,i44,I30,e44,e45").Select
Selection.Interior
Selection.ColorIndex = vbWhite
Selection.Pattern = xlSolid
End With
ActiveWorkbook.Route
ActiveWorkbook.Close SaveChanges:=False
Else
MsgBox "You must fill out all critical information highlighted in RED before Sending", vbCritical, "Critical"
Range("e5,j5,e6,d11,j9,j11,d28,I28,d29,d30,i29,k29 ,i44,I30,e44,e45").Interior.ColorIndex = vbRed
Selection.Pattern = xlSolid
End
End If
End Sub