this code working but can i paste which word was found in the Row to its opposite Row
i my excel in column F
i have list of words which is to be serached and paste the which word found in the row(colored yellow)
IT IS POSSIBLE TO DO LIKE THIS
example like:
if i found the word CABLE in the row
D E
ELECTRICAL,CABLE, EXTENSION, 12V, 5 METER | CABLE
BELT, V, 376IN OC, 0.65625IN WD, 0.40625IN THK | BELT
![]()
Sub FindAndColor() Dim cell As Variant Dim FndWhat As String Dim RegExp As Object Dim RngBeg As Range Dim RngEnd As Range Dim SearchWords As Variant Dim Word As Variant Set RngBeg = Range("F2") Set RngEnd = Cells(Rows.Count, "F").End(xlUp) If RngEnd.Row < RngBeg.Row Then Exit Sub SearchWords = Range(RngBeg, RngEnd).Value Set RngBeg = Range("D2") Set RngEnd = Cells(Rows.Count, "D").End(xlUp) If RngEnd.Row < RngBeg.Row Then Exit Sub Set RegExp = CreateObject("VBScript.RegExp") RegExp.IgnoreCase = True Application.ScreenUpdating = False For Each Word In SearchWords RegExp.Pattern = "\b" & Word & "\b" For Each cell In Range(RngBeg, RngEnd) If RegExp.Execute(cell).Count > 0 Then cell.Interior.ColorIndex = 6 cell.Interior.Pattern = xlSolid End If Next cell Next Word Application.ScreenUpdating = True End Sub











LinkBack URL
About LinkBacks
Register To Reply

Bookmarks