I'm trying to make a bingo game where if a user clicks a button: One selected
cell randomly posts a number between 1 to 50 at the same time that posted #
gets posted on Clip Board so everyone knows which #'s has been called out,
also find a matching # in Bingo Board and color mark(Red) and last when 1
full line is all colored cell (A4="BINGO !!!") (Hopefully I'm clear with what
I'm trying to do)

Random # gets posted on Cell=A3

there are 30 empty cells that I named "Clip_Board" (C3:E12)

also rectangular shaped 25 empty cells that I named "Bingo" (C15: G19) has
number 1 to 25 in random (Whenever I click a button called "Reset" I would
like all Clip_Boards to clear and Bingo to shuffle)

I have changed some of your requirements, for example I decided to expand
your "Clip Board" to all 50 numbers because with 50 numbers and running the
program 10,000 times the average number of calls required to fill in a 5
number line was 32 calls whether the Bingo Board was 1 - 50 numbers or 1 -
25 numbers. Therefore for more than half of your games you would not result
in a winner.

The "Clip Board" is therefore now C3:G12

The Restart() code is for your "Reset" button and CallNumber() is for the
button to print the next number.

Option Explicit

Public ball(0 To 50)
Public Calls As Integer

Sub ReStart()
Dim x As Integer
Dim r As Integer
Dim y As Integer
Dim n As Integer

Application.ScreenUpdating = False

'Initialise Bingo Balls
For x = 1 To 50
ball(x) = x
Next x

'Mix up Bingo Balls
For x = 1 To 50
Randomize
r = Rnd * 50
ball(0) = ball(x)
ball(x) = ball(r)
ball(r) = ball(0)
Next x

'Fill Bing Board
For x = 1 To 5
For y = 1 To 5
n = n + 1
Cells(x + 14, y + 2).Value = ball(n)
Next y
Next x

'Sort Bingo Board lines into ascending order
For x = 1 To 5
Range(Cells(x + 14, 3), Cells(x + 14, 7)) _
.Sort Key1:=Cells(x + 14, 3), _
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlLeftToRight
Next x

'Mix up Bingo Balls for game
For x = 1 To 50
Randomize
r = Rnd * 50
ball(0) = ball(x)
ball(x) = ball(r)
ball(r) = ball(0)
Next x



'Clear old called numbers
Range("C3:G12").ClearContents
Range("A3").ClearContents


Calls = 0

Application.ScreenUpdating = True

End Sub

Sub CallNumber()
Dim n As Integer

If Range("A4").Value < "" Then Exit Sub

Calls = Calls + 1
If Calls 50 Then Calls = 50

'Print latest Bingo Ball number
Range("A3").Value = ball(Calls)

'Print Bingo Ball number in Clip Board
n = Calls Mod 5 + 2
If n = 2 Then n = 7
Cells(Application.RoundUp(Calls / 5, 0) + 2, n).Value = ball(Calls)

End Sub

In cell C15 the formula for the Conditional Formatting is:

=COUNTIF($C$3:$G$12,C15)0

Copy it down and across to fill all the Bingo Board

In cell I15 I entered the formula:
=SUM(COUNTIF($C$3:$G$12,C15),COUNTIF($C$3:$G$12,D1 5),COUNTIF($C$3:$G$12,E15),COUNTIF($C$3:$G$12,F15) ,COUNTIF($C$3:$G$12,G15))

and copied it down to cell I19

In H15 I had

=IF(I15=5,"Bingo!!","")

which was copied down to H19

Cell A4 had the formula:

=IF(MAX(I15:I19)=5,"Bingo!!","")

If you want the Bingo Board to have only numbers 1 - 25 then change the
'Fill Bing Board loop to 25 instead of 50.


--
HTH

Sandy
In Perth, the ancient capital of Scotland


with @tiscali.co.uk


"Need help with sumif" wrote
in message ...
I'm trying to make a bingo game where if a user clicks a button: One
selected
cell randomly posts a number between 1 to 50 at the same time that posted
#
gets posted on Clip Board so everyone knows which #'s has been called out,
also find a matching # in Bingo Board and color mark(Red) and last when 1
full line is all colored cell (A4="BINGO !!!") (Hopefully I'm clear with
what
I'm trying to do)

Random # gets posted on Cell=A3

there are 30 empty cells that I named "Clip_Board" (C3:E12)

also rectangular shaped 25 empty cells that I named "Bingo" (C15: G19) has
number 1 to 25 in random (Whenever I click a button called "Reset" I would
like all Clip_Boards to clear and Bingo to shuffle)

oops! I forgot to say that I formatted the font colour in I15:I19 the same
as the background colour so that it does not show or hide column I


--
HTH

Sandy
In Perth, the ancient capital of Scotland


with @tiscali.co.uk


"Sandy Mann" wrote in message
...
I have changed some of your requirements, for example I decided to expand
your "Clip Board" to all 50 numbers because with 50 numbers and running
the
program 10,000 times the average number of calls required to fill in a 5
number line was 32 calls whether the Bingo Board was 1 - 50 numbers or 1 -
25 numbers. Therefore for more than half of your games you would not
result
in a winner.

The "Clip Board" is therefore now C3:G12

The Restart() code is for your "Reset" button and CallNumber() is for the
button to print the next number.

Option Explicit

Public ball(0 To 50)
Public Calls As Integer

Sub ReStart()
Dim x As Integer
Dim r As Integer
Dim y As Integer
Dim n As Integer

Application.ScreenUpdating = False

'Initialise Bingo Balls
For x = 1 To 50
ball(x) = x
Next x

'Mix up Bingo Balls
For x = 1 To 50
Randomize
r = Rnd * 50
ball(0) = ball(x)
ball(x) = ball(r)
ball(r) = ball(0)
Next x

'Fill Bing Board
For x = 1 To 5
For y = 1 To 5
n = n + 1
Cells(x + 14, y + 2).Value = ball(n)
Next y
Next x

'Sort Bingo Board lines into ascending order
For x = 1 To 5
Range(Cells(x + 14, 3), Cells(x + 14, 7)) _
.Sort Key1:=Cells(x + 14, 3), _
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlLeftToRight
Next x

'Mix up Bingo Balls for game
For x = 1 To 50
Randomize
r = Rnd * 50
ball(0) = ball(x)
ball(x) = ball(r)
ball(r) = ball(0)
Next x



'Clear old called numbers
Range("C3:G12").ClearContents
Range("A3").ClearContents


Calls = 0

Application.ScreenUpdating = True

End Sub

Sub CallNumber()
Dim n As Integer

If Range("A4").Value < "" Then Exit Sub

Calls = Calls + 1
If Calls 50 Then Calls = 50

'Print latest Bingo Ball number
Range("A3").Value = ball(Calls)

'Print Bingo Ball number in Clip Board
n = Calls Mod 5 + 2
If n = 2 Then n = 7
Cells(Application.RoundUp(Calls / 5, 0) + 2, n).Value = ball(Calls)

End Sub

In cell C15 the formula for the Conditional Formatting is:

=COUNTIF($C$3:$G$12,C15)0

Copy it down and across to fill all the Bingo Board

In cell I15 I entered the formula:
=SUM(COUNTIF($C$3:$G$12,C15),COUNTIF($C$3:$G$12,D1 5),COUNTIF($C$3:$G$12,E15),COUNTIF($C$3:$G$12,F15) ,COUNTIF($C$3:$G$12,G15))

and copied it down to cell I19

In H15 I had

=IF(I15=5,"Bingo!!","")

which was copied down to H19

Cell A4 had the formula:

=IF(MAX(I15:I19)=5,"Bingo!!","")

If you want the Bingo Board to have only numbers 1 - 25 then change the
'Fill Bing Board loop to 25 instead of 50.


--
HTH

Sandy
In Perth, the ancient capital of Scotland


with @tiscali.co.uk


"Need help with sumif" wrote
in message ...
I'm trying to make a bingo game where if a user clicks a button: One
selected
cell randomly posts a number between 1 to 50 at the same time that posted
#
gets posted on Clip Board so everyone knows which #'s has been called
out,
also find a matching # in Bingo Board and color mark(Red) and last when 1
full line is all colored cell (A4="BINGO !!!") (Hopefully I'm clear with
what
I'm trying to do)

Random # gets posted on Cell=A3

there are 30 empty cells that I named "Clip_Board" (C3:E12)

also rectangular shaped 25 empty cells that I named "Bingo" (C15: G19)
has
number 1 to 25 in random (Whenever I click a button called "Reset" I
would
like all Clip_Boards to clear and Bingo to shuffle)