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

mkashifq

New Member
Joined
Dec 28, 2017
Messages
3
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.
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
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
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.
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
Re: Help with find replace a word and change font color of only the replaced word.

Yes exactly, change font color partially! :)
 
Upvote 0

Forum statistics

Threads
1,224,815
Messages
6,181,136
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top