Previous Topic Next Topic

Conditional format "match range"

Solved/Closed
Netscur Posts 21 Registration date Thursday 23 July 2009 Status Member Last seen 6 April 2012 - 10 Aug 2010 à 10:27
 SadBingoDude - 24 Jan 2019 à 03:08
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


Related:

1 response

Réponse 1 / 1
rizvisa1 Posts 4478 Registration date Thursday 28 January 2010 Status Contributor Last seen 5 May 2022 766
10 Aug 2010 à 14:42
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
Netscur Posts 21 Registration date Thursday 23 July 2009 Status Member Last seen 6 April 2012
11 Aug 2010 à 10:15
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.
rizvisa1 Posts 4478 Registration date Thursday 28 January 2010 Status Contributor Last seen 5 May 2022 766
11 Aug 2010 à 10:24
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 23 July 2009 Status Member Last seen 6 April 2012
11 Aug 2010 à 13:17
Using a double negative trigger worked perfectly. Thank you for solving this!
JG
21 Oct 2013 à 06:43
I was having the same problem and this worked like a charm.

Thanks!
SadBingoDude
24 Jan 2019 à 03:08
Somebody still have an excel of this floating around? :) Have been looking for one for 8 hours straight :/

Similar discussions

Highlight cell if another cell contains specific text
Abbi -
vcoolio -

1 response