Search Column and Highlight Matches in a Range

tjtoad2

New Member
Joined
Jul 16, 2009
Messages
11
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.
 

Some videos you may like

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
Joined
Nov 7, 2006
Messages
8,350
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.
 

tjtoad2

New Member
Joined
Jul 16, 2009
Messages
11
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.


ABCDEF
10512
2212313
3411121321
4921222323

<tbody>
</tbody>
 

tjtoad2

New Member
Joined
Jul 16, 2009
Messages
11
Anyone have any ideas for a conditional formatting formula to address this? Thanks.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,659
Office Version
  1. 365
Platform
  1. Windows
Select B2:D4 & use
=isnumber(match(B2,$F$1:$F$4,0))
 

Watch MrExcel Video

Forum statistics

Threads
1,109,543
Messages
5,529,456
Members
409,878
Latest member
DDhol
Top