title: Find and Replace Values in Consecutive Cells Using VBA
body: <p>I am looking to replace values in three adjacent columns by row using values in another table. So far I've modified the code from this answer: <a href="https://www.mrexcel.com/forum/excel-questions/745557-visual-basic-applications-find-replace-multiple-values-multiple-cells-print.html" rel="nofollow noreferrer">https://www.mrexcel.com/forum/excel-questions/745557-visual-basic-applications-find-replace-multiple-values-multiple-cells-print.html</a> </p>
  
  <p>I want the names under columns A, B, and C to be searched for in columns G, H and I and replaced by the values listed under columns D, E and F (there are more values so the code needs to link to the columns, not just that particular range). I would want this code to only change the sequence if all three values in column A, B, and C match all three values of G, H, and I. </p>
  
  <p>The code kind of works for the first two columns, G and H (not sure why it won't on the column I), but if I have the same values in column A but different values in column B it isn't identifying that it needs to find the three adjacent cells together, it will find what is in column A and replace the value in column G with column D's value but it stops after that and replaces nothing else. </p>
  
  <p>My code: </p>
  
  <pre><code>Sub test()
  
  Dim NumV, AlphaV As String
  Dim irow As Long
  
  irow = Cells(1000000, 1).End(xlUp).Row
  
  Do Until irow = 0
      TskOwn = Cells(irow, 1)
      NewTskOwn = Cells(irow, 4)
      TskSup = Cells(irow, 2)
      NewTskSup = Cells(irow, 5)
      TskIni = Cells(irow, 3)
      NewTaskIni = Cells(irow, 6)
  
  If Cells(irow, 7) = TskOwn And Cells(irow, 8) = TskSup And Cells(irow, 9) = TskIni Then
      Application.ReplaceFormat.Interior.ColorIndex = (34)
      Columns(7).Replace What:=TskOwn, Replacement:=NewTskOwn, LookAt:=xlWhole, _
              SearchOrder:=xlByRows, MatchCase:=False
      Application.ReplaceFormat.Interior.ColorIndex = (22)
      Columns(8).Replace What:=TskSup, Replacement:=NewTskSup, LookAt:=xlWhole, _
              SearchOrder:=xlByRows, MatchCase:=False
      Application.ReplaceFormat.Interior.ColorIndex = (12)
      Columns(9).Replace What:=TskIni, Replacement:=NewTskIni, LookAt:=xlWhole, _
              SearchOrder:=xlByRows, MatchCase:=False
  End If
  
  irow = irow - 1
  Loop
  
  
  End Sub
  </code></pre>
  
  <p>What I have: </p>
  
  <pre><code>+--------+--------+---------+-------+-------+-------+--------+--------+---------+
  | A      | B      | C       | D     | E     | F     | G      | H      | I       |
  +--------+--------+---------+-------+-------+-------+--------+--------+---------+
  | Ty     | Sheri  | Richard | Name1 | Name2 | Name3 | Ty     | Sheri  | Richard |
  +--------+--------+---------+-------+-------+-------+--------+--------+---------+
  | Rachel | Sheila | Sheila  | Name4 | Name5 | Name2 | Rachel | Sheila | Sheila  |
  +--------+--------+---------+-------+-------+-------+--------+--------+---------+
  | Rachel | Bob    | Paul    | Name6 | Name7 | Name1 | Ty     | Sheri  | Richard |
  +--------+--------+---------+-------+-------+-------+--------+--------+---------+
  |        |        |         |       |       |       | Sarah  | Kyle   | Robert  |
  +--------+--------+---------+-------+-------+-------+--------+--------+---------+
  |        |        |         |       |       |       | Rachel | Bob    | Paul    |
  +--------+--------+---------+-------+-------+-------+--------+--------+---------+
  |        |        |         |       |       |       | Ty     | Sheri  | Richard |
  +--------+--------+---------+-------+-------+-------+--------+--------+---------+
  |        |        |         |       |       |       | Rachel | Sheila | Sheila  |
  +--------+--------+---------+-------+-------+-------+--------+--------+---------+
  |        |        |         |       |       |       | Rachel | Bob    | Paul    |
  +--------+--------+---------+-------+-------+-------+--------+--------+---------+
  |        |        |         |       |       |       | Ty     | Sheri  | Richard |
  +--------+--------+---------+-------+-------+-------+--------+--------+---------+
  |        |        |         |       |       |       | Ty     | Sheri  | Richard |
  +--------+--------+---------+-------+-------+-------+--------+--------+---------+
  |        |        |         |       |       |       | Rachel | Sheila | Sheila  |
  +--------+--------+---------+-------+-------+-------+--------+--------+---------+
  |        |        |         |       |       |       | Ty     | Sheri  | Richard |
  +--------+--------+---------+-------+-------+-------+--------+--------+---------+
  |        |        |         |       |       |       | Sarah  | Kyle   | Robert  |
  +--------+--------+---------+-------+-------+-------+--------+--------+---------+
  |        |        |         |       |       |       | Rachel | Sheila | Sheila  |
  +--------+--------+---------+-------+-------+-------+--------+--------+---------+
  </code></pre>
  
  <p>What I want: </p>
  
  <pre><code>+--------+--------+---------+-------+-------+-------+-------+-------+--------+
  | A      | B      | C       | D     | E     | F     | G     | H     | I      |
  +--------+--------+---------+-------+-------+-------+-------+-------+--------+
  | Ty     | Sheri  | Richard | Name1 | Name2 | Name3 | Name1 | Name2 | Name3  |
  +--------+--------+---------+-------+-------+-------+-------+-------+--------+
  | Rachel | Sheila | Sheila  | Name4 | Name5 | Name2 | Name4 | Name5 | Name2  |
  +--------+--------+---------+-------+-------+-------+-------+-------+--------+
  | Rachel | Bob    | Paul    | Name6 | Name7 | Name1 | Name1 | Name2 | Name3  |
  +--------+--------+---------+-------+-------+-------+-------+-------+--------+
  |        |        |         |       |       |       | Sarah | Kyle  | Robert |
  +--------+--------+---------+-------+-------+-------+-------+-------+--------+
  |        |        |         |       |       |       | Name6 | Name7 | Name1  |
  +--------+--------+---------+-------+-------+-------+-------+-------+--------+
  |        |        |         |       |       |       | Name1 | Name2 | Name3  |
  +--------+--------+---------+-------+-------+-------+-------+-------+--------+
  |        |        |         |       |       |       | Name4 | Name5 | Name2  |
  +--------+--------+---------+-------+-------+-------+-------+-------+--------+
  |        |        |         |       |       |       | Name6 | Name7 | Name1  |
  +--------+--------+---------+-------+-------+-------+-------+-------+--------+
  |        |        |         |       |       |       | Name1 | Name2 | Name3  |
  +--------+--------+---------+-------+-------+-------+-------+-------+--------+
  |        |        |         |       |       |       | Name1 | Name2 | Name3  |
  +--------+--------+---------+-------+-------+-------+-------+-------+--------+
  |        |        |         |       |       |       | Name4 | Name5 | Name2  |
  +--------+--------+---------+-------+-------+-------+-------+-------+--------+
  |        |        |         |       |       |       | Name1 | Name2 | Name3  |
  +--------+--------+---------+-------+-------+-------+-------+-------+--------+
  |        |        |         |       |       |       | Sarah | Kyle  | Robert |
  +--------+--------+---------+-------+-------+-------+-------+-------+--------+
  |        |        |         |       |       |       | Name4 | Name5 | Name2  |
  +--------+--------+---------+-------+-------+-------+-------+-------+--------+
  </code></pre>
  
  <p>What I'm getting:</p>
  
  <pre><code>+--------+--------+---------+-------+-------+-------+-------+-------+--------+
  | A      | B      | C       | D     | E     | F     | G     | H     | I      |
  +--------+--------+---------+-------+-------+-------+-------+-------+--------+
  | Ty     | Sheri  | Richard | Name1 | Name2 | Name3 | Name1 | Name2 |        |
  +--------+--------+---------+-------+-------+-------+-------+-------+--------+
  | Rachel | Sheila | Sheila  | Name4 | Name5 | Name2 | Name4 | Name5 |        |
  +--------+--------+---------+-------+-------+-------+-------+-------+--------+
  | Rachel | Bob    | Paul    | Name6 | Name7 | Name1 | Name1 | Name2 |        |
  +--------+--------+---------+-------+-------+-------+-------+-------+--------+
  |        |        |         |       |       |       | Sarah | Kyle  | Robert |
  +--------+--------+---------+-------+-------+-------+-------+-------+--------+
  |        |        |         |       |       |       | Name4 | Bob   | Paul   |
  +--------+--------+---------+-------+-------+-------+-------+-------+--------+
  |        |        |         |       |       |       | Name1 | Name2 |        |
  +--------+--------+---------+-------+-------+-------+-------+-------+--------+
  |        |        |         |       |       |       | Name4 | Name5 |        |
  +--------+--------+---------+-------+-------+-------+-------+-------+--------+
  |        |        |         |       |       |       | Name4 | Bob   | Paul   |
  +--------+--------+---------+-------+-------+-------+-------+-------+--------+
  |        |        |         |       |       |       | Name1 | Name2 |        |
  +--------+--------+---------+-------+-------+-------+-------+-------+--------+
  |        |        |         |       |       |       | Name1 | Name2 |        |
  +--------+--------+---------+-------+-------+-------+-------+-------+--------+
  |        |        |         |       |       |       | Name4 | Name5 |        |
  +--------+--------+---------+-------+-------+-------+-------+-------+--------+
  |        |        |         |       |       |       | Name1 | Name2 |        |
  +--------+--------+---------+-------+-------+-------+-------+-------+--------+
  |        |        |         |       |       |       | Sarah | Kyle  | Robert |
  +--------+--------+---------+-------+-------+-------+-------+-------+--------+
  |        |        |         |       |       |       | Name4 | Name5 |        |
  +--------+--------+---------+-------+-------+-------+-------+-------+--------+
  </code></pre>
  
  <p>Any help would be appreciated! Not sure what I'm doing wrong. </p>
  
  <p>Thanks!!</p>