Find and replace background color along with value

[Tim Hanson]

I use the code to update a list of original values in a column with new values from a different column on a different sheet, the new values I have in a column with a background color, is it possible to not just update the value in the original list but to add the background color of the new value as well?

The original list can be long and the added background color would be helpful in seeing what was updataed

Thanks

Sub MatchReplace()
    Dim FindValues As Variant, ReplaceValues As Variant
    Dim wsFR As Excel.Worksheet, wsTarget As Excel.Worksheet
    Dim lRow As Long, i As Long

    Sheets("Updated_UnMatched").Select
     
    Set wsFR = ThisWorkbook.Worksheets("Updated_UnMatched")
    Set wsTarget = ThisWorkbook.Worksheets("OriginalData")
   
        FindValues = wsFR.Range("C1:C" & lRow).Value
  ReplaceValues = wsFR.Range("D1:D" & lRow).Value
   
   
    With wsTarget
      If IsArray(FindValues) Then
         For i = 2 To UBound(FindValues)
              .Columns("B:B").Replace FindValues(i, 1), ReplaceValues(i, 1), xlWhole, xlByColumns, False
         Next i
      Else
       
      End If
    End With
   
End Sub

 

[Chihiro]

From what I see in the code you have... you are missing pieces?
Ex. lRow isn't defined.

In any case, you can add another variable that grabs Range portion only and then set variablerange.INTERIOR.COLOR

 

[Tim Hanson]

Hello, Chihiro, thanks for the responce

I put lRow in there by accident it can be ignored.

Do you mean the range of the cell of the Original list that will be updated?

If so not sure how I capture that

 

[Chihiro]

Can you upload sample file with the macro?

I'm having trouble seeing how your code works in practice. Doesn't seem to do do what you describe.