VBA code to find and replace in each row based on cell value

Hi JamesPhImp,

You can try this script, let me explain what the script should do.

The script is designed to cycle through a specific worksheet in the Excel workbook and perform a find and replace operation in each row based on the values in columns B and C.

It will look for non-empty cells in both columns B and C, and if found, it will highlight the entire row, replace the value in column B with the value in column C for that row, and then move on to the next row.
The script will continue this process until there is no value in column A, indicating the end of the data.

Sub FindAndReplaceInEachRow()
Dim ws As Worksheet
Dim lastRow As Long
Dim i As Long

' Set the worksheet to work with
Set ws = ThisWorkbook.Sheets("Sheet1") ' Replace "Sheet1" with your sheet name

' Find the last row with data in column A
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

' Loop through each row and perform find and replace
For i = 1 To lastRow
' Check if there is a value in both column B and C for the current row
If Not IsEmpty(ws.Cells(i, "B").Value) And Not IsEmpty(ws.Cells(i, "C").Value) Then
' Highlight the entire row to indicate that the operation is being performed
ws.Rows(i).Interior.Color = RGB(255, 255, 0) ' Yellow color, change as needed

' Perform the find and replace for column B and C of the current row
ws.Cells(i, "B").Value = ws.Cells(i, "C").Value
End If
Next i

' Clear the highlighting after the process is done
ws.Cells.Interior.ColorIndex = xlNone
End Sub

You can use the script by following these steps in Excel:
1. Open your Excel workbook.
2. Press "ALT + F11" to open the Visual Basic for Applications (VBA) editor.
3. Go to "Insert" > "Module" to add a new module.
4. Paste the code into the module.
5. Close the VBA editor.

6. Press "ALT + F8" to open the "Macro" dialog box.
7. Select "FindAndReplaceInEachRow" and click "Run."