Finding matches within groups of cells [Closed]

Report
-
Posts
2758
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 17, 2021
-
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!!

1 reply

Posts
2758
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 17, 2021
462
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

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!