Find replace a word and change font color of only the replaced word.

[mkashifq]

Hello Everyone,

Need help with the VBA code mentioned in the end.

Objective: To find all occurrences of text from Column A in Sheet 1 and replace them with respective value from Column B in Sheet 2 and highlight the replaced word(s).

Issues with existing code: The code is not searching it as a complete word. Its finding it even if the values in column A is part of some word in sheet 2. Its also highlighting the entire cell while i want it to highlight only the replacement.


Here is sample data:
Sheet 1
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Assist[/TD]
[TD]Assisted[/TD]
[/TR]
[TR]
[TD]Asstmt[/TD]
[TD]Assesment[/TD]
[/TR]
[TR]
[TD]Cat6[/TD]
[TD]Category 6[/TD]
[/TR]
</tbody>[/TABLE]

Sheet 2:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]AssistHelix KIDs Blunt-tip Spring AssiStEd Scissors[/TD]
[TD]6\ Spring-assiSt Scissors"[/TD]
[/TR]
[TR]
[TD]Sequins/SPAngles AsStmt[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 332"]
<tbody>[TR]
[TD="class: xl66, width: 332"]Cat6 Gigabit Snagless PAtch Cable[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Here's the code.

Dim fndList As Integer
Dim rplcList As Integer
Dim tbl As ListObject
Dim myArray As Variant
Dim x As Long


'Create variable to point to your table
  Set tbl = Worksheets("sheet1").ListObjects("Table3")


'Create an Array out of the Table's Data
  Set TempArray = tbl.DataBodyRange
  myArray = Application.Transpose(TempArray)
  
'Designate Columns for Find/Replace data
  fndList = 1
  rplcList = 2


'Loop through each item in Array lists
  For x = LBound(myArray, 1) To UBound(myArray, 2)
    'Loop through each worksheet in ActiveWorkbook (skip sheet with table in it)
      For Each sht In ActiveWorkbook.Worksheets
        If sht.Name <> tbl.Parent.Name Then
          
          sht.Cells.Replace What:=myArray(fndList, x), Replacement:=myArray(rplcList, x), _
            LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
            SearchFormat:=False, ReplaceFormat:=True
             Application.ReplaceFormat.Font.Color = 3
        End If
      Next sht
  Next x


End Sub

 

[crazydragon84]

Re: Help with find replace a word and change font color of only the replaced word.

for finding entire cell content, you just need to change LookAt:=xlPart to LookAt:=xlwhole. Not sure what you mean by Its also highlighting the entire cell while i want it to highlight only the replacement.

 

[mkashifq]

Re: Help with find replace a word and change font color of only the replaced word.

I want to highlight only the words which are replaced in the output. currently its filling in the Cell that has the value.

 

[crazydragon84]

Re: Help with find replace a word and change font color of only the replaced word.

you mean change font color? i don't think you can partially fill a cell?

 

[mkashifq]

Re: Help with find replace a word and change font color of only the replaced word.

Yes exactly, change font color partially!