Conditional format "match range" [Solved/Closed]

Posts
21
Registration date
Thursday July 23, 2009
Status
Member
Last seen
April 6, 2012
- - Latest reply:  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


See more 

1 reply

Best answer
Posts
4475
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
752
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

Say "Thank you" 3

A few words of thanks would be greatly appreciated. Add comment

CCM 6324 users have said thank you to us this month

Netscur
Posts
21
Registration date
Thursday July 23, 2009
Status
Member
Last seen
April 6, 2012
-
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
Posts
4475
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
752 -
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
Posts
21
Registration date
Thursday July 23, 2009
Status
Member
Last seen
April 6, 2012
-
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!
Somebody still have an excel of this floating around? :) Have been looking for one for 8 hours straight :/