title: Replacing/Updating/Modify Cell Value if another cell from same column contain X
body: <p><strong>Excel Background</strong><br>
  I got 2 excel column and long records of rows. (example only, as I have more than 10 column)</p>
  
  <p>Column A - It is Either "Yes or No"
  Column B - Is a remark. Can type anything I need</p>
  
  <p><br><strong>How it works</strong><br>
  This is an ordering system, if date is delayed, Column A will show "Yes"
  If no Delay, it will show "No"</p>
  
  <p><br>
  <strong>I need</strong><br>
  I need the VBA code to perform the following:</p>
  
  <p>If column B2 contains text "No Delay", Column A2 will change from "Yes" to "No"</p>
  
  <p><br><strong>What I have tested</strong><br></p>
  
  <pre><code>    Private Sub UpdateColumnA()
      Dim x As Long
      For x = 4 To 65536
      Dim InStr As String
  
      If InStr(1, Sheet1.Range("$M$" &amp; x), "No Delay") Then
          Sheet1.Range("$K$" &amp; x) = Sheet1.Range("$K$" &amp; x) &amp; "No"
          End If
  
      Next
      End Sub
  </code></pre>
  
  <p>Problem I met above is that, Column A2 "Yes" will become "<strong>YesNo</strong>". I require to replace the Yes to No instead of adding to the existing text.
  <br><br>And if B2 text "<strong>No Delay</strong>" does not match exactly, it will not work at all, hence I want it to be <strong>non case sensitive</strong>, such as "nO Delay" "No DeLAy" will also work. </p>