title: search compare columns sheet 1 & sheet 2 and change a cell in sheet 1
body: <p>I really need some help.</p>
  
  <p>Problem: I have a workbook with 2 worksheets. Both sheets has headers. Sheet1 is a list of account numbers in column A and the same for sheet 2 column A. Now, what I need to do is this:</p>
  
  <ul>
  <li>if I place a date in column AI in sheet 2 for a specific account number, then find the corresponding account number in sheet 1 and place the word "Complete" in column Y for that account.  </li>
  </ul>
  
  <p>I hope I explained this enough. Below is what I came up with so far, but got stuck:</p>
  
  <pre><code>Sub UpdateTBP()
  Dim i
  Dim j
  Dim k
  Dim LastRow
  Dim LastRow2
  
  LastRow = Sheets("Portfolio").Cells(Rows.Count, 1).End(xlUp).Row
  LastRow2 = Sheets("TBP").Cells(Rows.Count, 1).End(xlUp).Row
  
  For i = 2 To (LastRow - 1)
    For j = 2 To (LastRow2 - 1)
      If Sheets("Portfolio").Cells(i, 1) = Sheets("TBP").Cells(j, 1).value Then
        For k = 35 To 35
          If Sheets("TBP").Cells(j, 35) &lt;&gt; "" Then
            Sheets("Portfolio").Cells(i, Y).value = "Complete"
          End If
        Next
      End If
    Next
  Next
  ThisWorkbook.Save
   End Sub                                
  </code></pre>