Hello,
I am trying to tweak the following VBA code. What I need to happen is for excel to look at the list of names in Column "A" Sheet "Name" and highlight all cells in Column "D" on Sheet "Match" that match the "A" list, a partial name match should be highlighted.
Both the sheets ("Name" and "Match") can have a different number of rows that can vary, so I also need to make sure it searches all names and reviews for all possible matches.
It currently is not finding anything, though there are a number or direct matches and more than 1,000 partial matches.
I am trying to tweak the following VBA code. What I need to happen is for excel to look at the list of names in Column "A" Sheet "Name" and highlight all cells in Column "D" on Sheet "Match" that match the "A" list, a partial name match should be highlighted.
Both the sheets ("Name" and "Match") can have a different number of rows that can vary, so I also need to make sure it searches all names and reviews for all possible matches.
It currently is not finding anything, though there are a number or direct matches and more than 1,000 partial matches.
Code:
Sub CheckNames()
Dim Cell As Range, cRange As Range, sRange As Range, Rng As Range, FindString As String
LastRow1 = Sheets("Match").Cells(Rows.Count, "D").End(xlUp).Row
LastRow2 = Sheets("Name").Cells(Rows.Count, "A").End(xlUp).Row
Set cRange = Sheets("Match").Range("D3:D" & LastRow1)
Set sRange = Sheets("Name").Range("A1:A" & LastRow2)
For Each Cell In cRange
FindString = Cell.Value
With sRange
Set Rng = .Find(What:=FindString, _
After:=.Cells(1), _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False)
If Not Rng Is Nothing Then
Rng.Interior.ColorIndex = 6
Cell.Interior.ColorIndex = 6
End If
End With
Next
End Sub