title: Change cell values if a match is found
body: <p>I have a worksheet that has a textbox with a button that will be assigned to a macro. In this workbook there is another worksheet that holds the master data in a table. The textbox will have a job#. What I'm trying to do is lookup the job# in the job# column (column B/named range: "JobCol_Master") in the master table, and if a match is found, then in that row, verify a value based on what's in column D. If true, then change cell values in particular columns in that row.</p>
  
  <p>I am getting a type mismatch error on line <code>Range(Cells(cell, 11)).Value = "Test"</code>. I am also getting an error when values don't match. </p>
  
  <p>I appreciate any guidance anyone can give me.</p>
  
  <pre><code>Option Explicit
  
  Sub IDCloseJob()
  
  Dim ws As Worksheet
  Dim MasterData As Range
  Dim sourceID As Range
  Dim cell As Range, row As Range, JobCol As Range
  Dim Txt As String
  
  Txt = ThisWorkbook.Worksheets("ID").TextBoxID.Text
  Set MasterData = ThisWorkbook.Worksheets("Jobs").Range("MasterData")
  
  
  If Txt &lt;&gt; "" Then
      With MasterData
          For Each cell In .Range("JobCol_Master")
              'If job# matches textbox and if job# has value equal to "ID" in Column D then...
              If cell.Text = Txt Then
                Cells(cell.row, 11).Value = "Test"
              Else
                MsgBox "Job# not found."
              End If
          Next cell
      End With
   End If
  
  End Sub
  </code></pre>
  
  <p>Updated Code:</p>
  
  <pre><code>Sub IDCloseJob()
  
  Dim MasterData As Range
  Dim sourceID As Range
  Dim cell As Range, row As Range, JobCol As Range
  Dim Txt As String
  
  Txt = ThisWorkbook.Worksheets("ID").TextBoxID.Text
  Set MasterData = ThisWorkbook.Worksheets("Jobs").Range("MasterData")
  
  
  If Txt &lt;&gt; "" Then
      With MasterData
          For Each cell In .Range("JobCol_Master")
              'If job# matches textbox and if job# has value equal to "ID" in Column D then...
              If cell.Text = Txt Then
                Cells(cell.row, 11).Value = "Test"
              Else
                MsgBox "Job not found."
                Exit Sub
              End If
          Next cell
      End With
   End If
  End Sub
  </code></pre>
  
  <p>The above code if true doesn't complete the true statement <code>Cells(cell.row, 11).Value = "Test"</code>. It bring up the <code>MsgBox</code> then exits the sub. Not sure why...code looks right. If I leave out the false statement it completes my true statement.</p>