Conditional format "match range" [Solved/Closed]

Netscur 21 Posts Thursday July 23, 2009Registration date April 6, 2012 Last seen - Aug 10, 2010 at 10:27 AM - Latest reply:  JG
- Oct 21, 2013 at 06:43 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


See more 

5 replies

Best answer
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Aug 10, 2010 at 02:42 PM
3
Thank you
Could you please upload a sample EXCEL file WITH sample data, macro, formula , conditional formatting etc on some shared site like http://www.speedyshare.com/ , 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

Thank you, rizvisa1 3

Something to say? Add comment

CCM has helped 1666 users this month

Netscur 21 Posts Thursday July 23, 2009Registration date April 6, 2012 Last seen - Aug 11, 2010 at 10:15 AM
http://www.speedyshare.com/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.
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - 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))))
Netscur 21 Posts Thursday July 23, 2009Registration date April 6, 2012 Last seen - Aug 11, 2010 at 01:17 PM
Using a double negative trigger worked perfectly. Thank you for solving this!
I was having the same problem and this worked like a charm.

Thanks!