macro to check if a particular range of cells contains a value

[acquirersam]

Hello guys,


I am trying to build a vb script macro in excel for sending a email. If the user inputs the value in column "F" as "No" , the script checks if the cell L2 contain a value "0". If it contains "1" or any other value, it will not prompt for sending email.
The script works perfectly except for that i was not able to define the range L2:L200.
This is what i was trying to do:
i.e., if the user enters "NO" in any row of column "F" and if the same row in the column "L" contains the value as "0", it should prompt for sending email. Otherwise, It should not.
Please some one help me as i have only limited knowledge in excel vb macros. I have left a space between the code where the range is defined.

Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" _
                   (ByVal hwnd As Long, ByVal lpszOp As String, _
                    ByVal lpszFile As String, ByVal lpszParams As String, _
                    ByVal LpszDir As String, ByVal FsShowCmd As Long) _
                    As Long
-------------------------------------------------------------------------------------------------------------------------
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim lngResponse As Long
Dim Mail_Object, Email_Subject, Email_Send_To, Email_Cc, Email_Bcc, Email_Body, Email_Body1, Email_Body2, Email_Body3 As String
  If Left(Target.Address, 2) = "$F" Then
  If Target.Value = "No" Then 


If Range("L2").Value = "0" Then

 
  lngResponse = MsgBox("Draft and send an email now ?", vbYesNo)
  If lngResponse = vbYes Then
Email_Subject = "Approval needed to process change order for PO " & Range("$C" & Right(Target.Address, 2)).Value & ""
    Email_Send_To = ""
    Email_Cc = ""
    Email_Bcc = ""
    Email_Body = "Hi ,"
    Email_Body1 = "   Please approve to process change order for PO# " & Range("$A" & Right(Target.Address, 2)).Value & ": "
    Email_Body2 = "   Actual price on the PO: $" & Range("$B" & Right(Target.Address, 2)).Value & ""
    Email_Body3 = " , Vendor quoted price: $" & Range("$C" & Right(Target.Address, 2)).Value & "" & "     " & "     " & "     " & "     " & "     " & "     " & "     " & "     "
    Mail_Object = "mailto:" & Email_Send_To & "?subject=" & Email_Subject & "&body=" & Email_Body & Email_Body1 & Email_Body2 & Email_Body3 & "&cc=" & Email_Cc '& "&bcc=" & Email_Bcc
    'On Error GoTo debugs
    ShellExecute 0&, vbNullString, Mail_Object, vbNullString, vbNullString, vbNormalFocus 
  End If
  End If
  End If
  End If
End Sub

 

[VoG]

Instead of

If Range("L2").Value = "0" Then

Try

If Target.Offset(, 6).Value = 0 Then

 

[acquirersam]

Thankyou verymuch!!!! its working perfect!!

 

[VoG]

Great news!

As you are a first time poster, thank you for using code tags. It makes code so much easier to read.

 

[acquirersam]

Thankyou!. Thats a great news for me!

And.. i need another help from you. currently in that code, i am using mailto script for sending email which dont accept html tags. I tried CDO method which needs a from field. what i exactly need is to send a line by line email with certain information.

Can you please help me with this?

 

[VoG]

This is a great place to start http://www.rondebruin.nl/sendmail.htm

 

[acquirersam]

Thanks again!!, I am getting weird error now:

The following error is highlighted in the line ."Value= ......"

Run-time error '1004'
Application-defined or object defined error

when I use this code,:

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("C2:C100")) Is Nothing Then

            With Target(1, 4)



                .Value = "=IF(E2:E100="""","""",IF(E2:E100=""No"",""Not Applicable"",IF(E2:E100=""Yes"",""---Select---"","""")))"
              
                

            End With
            
            End If
            
        
            End Sub

The basic idea of this code is to enter "select " or "Not Applicable" in the range F2:F100 if some value is entered is entered in C2:C100 and considers the value of E2:E100 before auto-populating the F2:F100 field.

This script is used in the sheet 2 of the work book.

Can you please help me?

 

[VoG]

Try .Formula instead of .Value

 

[acquirersam]

Dear VOG, now i got the same eror... is there any way to disable this runtime error from showing up?? i googled it but could not find a suitable one... nothing is working

 

[VoG]

You would need something like

With Target(1, 4).Resize(99)
    .Formula = "=IF(E2="""","""",IF(E2=""No"",""Not Applicable"",IF(E2=""Yes"",""---Select---"","""")))"
End With