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

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

---

Does the fixed cell "L2" need to be 0 for any of rows to prompt for email? Or do you mean "only prompt for email if the L column in the changed row is 0"? If so, here's the code:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    'If column is F and entry is "no", case insensitive
    If Target.Column = 6 And UCase(Target.Value) = "NO" Then
        'If the L column on the changed cell's row equals 0
        If Sh.Cells(Target.Row, 12) = 0 Then
            'Prompt for email code here
        End If
    End If
End Sub

Edit: Missed an End If