Conditional format "match range" [Solved/Closed]

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

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
760
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
Thank you

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

CCM 2835 users have said thank you to us this month

Posts
21
Registration date
Thursday July 23, 2009
Status
Member
Last seen
April 6, 2012

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.
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
760
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))))
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 :/