Finding matches within groups of cellsClosed

Question

Sorry for the confusing title! An example should clarify.  Here is a link to an image of the excel program I reference in my question: 

http://i.stack.imgur.com/QYyyY.png 

     Basically, I want to Excel to look through a row in the green area, and search for any matches within the same row in the pink area. For example, in row 1, cells C1 and E1 would be highlighted, as their values (3 and 5, respectively) match values found in the pink area in the same row. 

     If this is not possible, it would be helpful if I could just make an 'if' statement that reported "Match" if any of the cells in the green area matched any of the cells in the pink area within the same row, or otherwise report "No Match". For example, row 1 would report "Match" as two of the values are identical between the green and pink. Row 5, however, would report "No Match" as none of the green values match any of the pink values within row 5. I tried using =if(or(A1,B1,C1,D1,E1,F1)=or(H1,I1,J1...),"Match", "No Match", but that seemed to always result in a "Match" for any pink row that had any numerical cell value at all... 

Any help would be greatly appreciated!!

TrowaD · Answer

Hi Jack,

You didn't specify how you would like to highlight matched values in the green area, so I chose to make them bold.

Sub HMV()
Dim RowI, ColI, lRow As Integer
RowI = 0
lRow = Range("A" & Rows.Count).End(xlUp).Row
Do
RowI = RowI + 1
For Each cell In Range(Cells(RowI, 1), Cells(RowI, 6))
ColI = 7
Do
ColI = ColI + 1
If cell.Value = Cells(RowI, ColI).Value Then cell.Font.Bold = True
Loop Until ColI = 15
Next cell
Loop Until RowI = lRow
End Sub
Best regards,
Trowa

Finding matches within groups of cells

1 response

Similar discussions

Subscribe To Our Newsletter!