Solved I'd like to modify this macro to highlight rows in a sheet

January 6, 2017 at 08:18:08
Specs: Windows 7
I have the below macro to find key words in Column V in Sheet1 based on a list in Sheet2 and copy to Sheet3. I'd like to instead just highlight the rows in Sheet1 where the key words are found. Thank you!!

Sub KeyWordHunter()
'Determine last row with data in Sheet 2 Column A
lastSrc_rw = Sheets(2).Range("A" & Rows.Count).End(xlUp).Row
'Loop through list of keywords
For Each keyword In Sheets(2).Range("A1:A" & lastSrc_rw)
'Search for each keyword, copy row if found
With Sheets(1).Columns(22)
Set c = .Find(keyword, LookIn:=xlValues, lookat:=xlPart)
If Not c Is Nothing Then
firstAddress = c.Address
Do
'Find next open row in Sheet 3
nxtDst_rw = Sheets(3).Range("A" & Rows.Count).End(xlUp).Row + 1
'Copy row containing found car
c.EntireRow.Copy Destination:=Sheets(3).Range("A" & nxtDst_rw)
'Search for same car again, stop when no more found
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
Next
End Sub


See More: Id like to modify this macro to highlight rows in a sheet

Reply ↓  Report •


#1
January 6, 2017 at 09:10:51
✔ Best Answer
Assuming yellow is OK?

Sub KeyWordColor()
'Determine last row with data in Sheet 2 Column A
  lastSrc_rw = Sheets(2).Range("A" & Rows.Count).End(xlUp).Row
'Loop through list of keywords
    For Each keyword In Sheets(2).Range("A1:A" & lastSrc_rw)
'Search for each keyword, Highlight row if found
      With Sheets(1).Columns(22)
        Set c = .Find(keyword, LookIn:=xlValues, lookat:=xlPart)
         If Not c Is Nothing Then
           firstAddress = c.Address
             Do
              c.EntireRow.Interior.ColorIndex = 6
'Search for same car again, stop when no more found
              Set c = .FindNext(c)
             Loop While Not c Is Nothing And c.Address <> firstAddress
        End If
      End With
    Next
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Reply ↓  Report •

#2
January 6, 2017 at 09:15:48
This worked, thank you!!!

Reply ↓  Report •
Related Solutions


Ask Question