# Search Column and Highlight Matches in a Range

##### New Member
I have a 10 x 10 range of cells that are numbered 1-100 (Squares for NCAA Pool). On another tab in the same workbook I have a "winners column" which is formula based that returns which cell won based on a search. To provide a visual of how many different cells have won I would like to highlight each cell in the range which matches the number in the "winners column".

There may be duplicates which would make it even more interesting if I could highlight different colors based on the number of times the cell won.

Any ideas would be appreciated. Thanks.

### Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

#### Special-K99

##### Well-known Member
Not enough info.
Are you match by row where, e.g. A2:J2 = L2, A3:J3 = L3 ?

Try this

Select the range to highlight (in this case the grid)

Conditional Formatting
New Rule
Use a formula to determine...

=(A1:CJ1=\$L1)

format as required

where L1 is the winners column

This will highlight all the cells that match the row in column L the same colour.

If the winners column is just one number change the value from \$L1 to \$L\$1

Gonna need some example data to highlight different colours as it's not clear.

##### New Member
Special-K99,
Thanks for the reply. Below is an example of a section of my grid. As I mentioned this is setup as a NCAA Pool. So depending on the Final score of each basketball game I take the last digit of the winning team (B1:D1) and last digit of the losing team (A2:A4) and then find the winning square. Example Score: 75-72 = Winning Square C2. The winning numbers are filled in Column F.

I would now like to highlight each cell in my range (B1:D4) that won.

 A B C D E F 1 0 5 1 2 2 2 1 2 3 13 3 4 11 12 13 21 4 9 21 22 23 23

<tbody>
</tbody>

##### New Member
Anyone have any ideas for a conditional formatting formula to address this? Thanks.

#### Fluff

##### MrExcel MVP, Moderator
Select B2:D4 & use
=isnumber(match(B2,\$F\$1:\$F\$4,0))

Replies
0
Views
48
Replies
2
Views
47
Replies
16
Views
99
Replies
6
Views
105
Replies
3
Views
79