Bingo Board Formula

[KiwiNan]

Hi Im new here so looking for some help?

I have column of numbers 1-90 & 2 full playing cards for the bingo game - all i want to do is highlight a number in my column and it will highlight the numbers on my bingo cards - any help?

Thanks

 

[etaf]

you will need VBA to do that and colour a cell based on the fill colour , as far as i know - maybe wrong

can you just put say a CROSS next to the numbers called ??

and USE
=COUNTIFS($A$2:$A$91,D2,$B$2:$B$91,"X")>0

Book5
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Numbers	Called		Card1						card2
2	1			1	10	20	40	60		2	11	21	41	61
3	2			3	30	70	80	90		4	31	71	81	89
4	3			2	4	6	8	7		3	5	7	9	8
5	4			21	22	23	24	25		22	23	24	25	26
6	5			9	11	13	34	55		10	12	14	35	56
7	6
8	7
9	8
10	9
11	10
12	11	x
13	12
14	13
15	14	x
16	15
17	16
18	17
19	18
20	19	x
21	20
22	21
Sheet1

Cell Formulas
Range	Formula
J2:N2,J4:N6,J3:M3	J2	=D2+1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D2:N6	Expression	=COUNTIFS($A$2:$A$91,D2,$B$2:$B$91,"X")>0	text	YES

 

[KiwiNan]

you will need VBA to do that and colour a cell based on the fill colour , as far as i know - maybe wrong

can you just put say a CROSS next to the numbers called ??

and USE
=COUNTIFS($A$2:$A$91,D2,$B$2:$B$91,"X")>0

Book5
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Numbers	Called		Card1						card2
2	1			1	10	20	40	60		2	11	21	41	61
3	2			3	30	70	80	90		4	31	71	81	89
4	3			2	4	6	8	7		3	5	7	9	8
5	4			21	22	23	24	25		22	23	24	25	26
6	5			9	11	13	34	55		10	12	14	35	56
7	6
8	7
9	8
10	9
11	10
12	11	x
13	12
14	13
15	14	x
16	15
17	16
18	17
19	18
20	19	x
21	20
22	21
Sheet1

Cell Formulas
Range	Formula
J2:N2,J4:N6,J3:M3	J2	=D2+1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D2:N6	Expression	=COUNTIFS($A$2:$A$91,D2,$B$2:$B$91,"X")>0	text	YES

thanks i will give it a go

 

[KiwiNan]

thanks i will give it a go

I found this code to highlight a cell by right clicking on it.. and I can use the left click to clear it

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
Target.Interior.ColorIndex = 2
End Sub

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
Target.Interior.ColorIndex = 3
End Sub

 

[etaf]

ok, glad its solved , I dont provide VBA solutions on the forum,

 

[KiwiNan]

I found this code to highlight a cell by right clicking on it.. and I can use the left click to clear it

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
Target.Interior.ColorIndex = 2
End Sub

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
Target.Interior.ColorIndex = 3
End Sub

How can i change this so when i right click on the cell it will bring up "X"?