Conditional format "match range"

Solved/Closed
Netscur Posts 21 Registration date Thursday July 23, 2009 Status Member Last seen April 6, 2012 - Aug 10, 2010 at 10:27 AM
 SadBingoDude - Jan 24, 2019 at 03:08 AM
I am stumped and can't find an answer to this issue for Excel 2010

The best way I can describe this is that I am attempting to build an Excel based Bingo Card game. In column A ($A10:$A60) there are drawn numbers. Within cells $C3:$G7 is the bingo 'card', and each of these 25 cells contains a hard coded number value.

My goal is that if any number within the range $C3:$G7 is found in the range $A10:$A60, the cell will be filled with a color (green).

I've tried using conditional formatting with a formula but it doesn't seem to allow a cell to match to a "range" of cells.

Any thoughts or ideas would be appreciated.
-Netscur


1 reply

rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
Aug 10, 2010 at 02:42 PM
Could you please upload a sample EXCEL file WITH sample data, macro, formula , conditional formatting etc on some shared site like https://authentification.site , http://docs.google.com, http://wikisend.com/ , http://www.editgrid.com etc
A N D post back here the link to allow better understanding of how it is now and how you foresee. Based on the sample book, could you re-explain your problem too
3
Netscur Posts 21 Registration date Thursday July 23, 2009 Status Member Last seen April 6, 2012
Aug 11, 2010 at 10:15 AM
https://authentification.site/files/23755649/Bingo_Book.xlsx

This is a single worksheet.
Column A represents the 'bingo numbers drawn' (in theory the match range should be for $A:$A as it could be ever expanding).

In cells C3: G25 are three templates for `bingo cards'. I expect a user would have to enter their numbers in manually until I wrote the program to send out electronic cards.



The only method I could construct to make this objective functional was to duplicate the `card data' in cells J3:N25. Then I used a simple MATCH formula to lookup if the number was drawn (found in column A). If it is found/true the cell space will be populated with an "X" or left it blank if the lookup criteria is false.

I am missing something basic or simple to make this concept function without duplicating the card templates. Your expertise is appreciated.
0
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
Aug 11, 2010 at 10:24 AM
How about some thing like this

IF you hightlight range C3:G7 and put this as conditional format

=AND(C3<>"", NOT(ISERROR(MATCH(C3,$A:$A,0))))
1
Netscur Posts 21 Registration date Thursday July 23, 2009 Status Member Last seen April 6, 2012
Aug 11, 2010 at 01:17 PM
Using a double negative trigger worked perfectly. Thank you for solving this!
0
I was having the same problem and this worked like a charm.

Thanks!
0
Somebody still have an excel of this floating around? :) Have been looking for one for 8 hours straight :/
0