# mastermind by YEhmBCM

VIEWS: 0 PAGES: 10

• pg 1
0
0
10
0
9
Enter the number
0
of different colours
to use:                            8
6                0
Note: colours may
still be entered, but
7
the code should not       0
include them.
Colours which may                  6
be used in the code
are shown below:          0
5
Red            Red      0
Blue           Blue              4
Green           Green 0
Yellow           Yellow            3
Orange                0
Orange
Brown            Brown             2
Black 0
White             1
Instructions:
Click New Game (If not visible click Show
Code). If you want to set a code, click in the
first of the squares in between the Hide Code
and New Game buttons. To enter colours,
just type in their names, or click on the
coloured buttons below. Only use the
colours shown at the bottom left of the
screen. To include more or less colours,
enter a number between 2 and 8 in the grey
box. Then click on Hide Code. If you want
an automatic code writing, leave the squares
blank and click Hide Code, then click Get
Random Code.

During play:
Enter colours as described above. When
happy with your guess, press the
corresponding Check button. This will mark
your guess and you may proceed.
How does it work?

The fomulas…
The most important aspect is the marking of guesses. This was achieved by listing the
possible colours in a table alongside the number of each used by both the code and the
guesses. Logic functions were used to give an overall score for the number of correct
colours. For instance, if the code included green and the guess did not, no marks would
given, and if the guess used blue and the code did not, no marks were given. If both th
code and the guess used the same colour, the number of times the colour appeared in the
guess up to and including the number it appeared in the guess determined the final mark.
For instance, if there were two greens in the code and three in the guess, the score wou
be two, and if there were only one green in the guess the score would be 1. This gave t
overall number of blacks and whites, but did not differentiate between them. A direct
comparison between the guess and the code then gave the number of black pegs, and thus t
remainder were white.

The formats…
Using Conditional Formatting I was able to make a cell change to black or white dependin
on its contents, "b" or "w". This works well when there are a maximum of three conditio
and corresponding formats, but I had to resort to altering the code behind the sheet (I
found details of it from www.exceltip.com) which expands the number of conditions
indefinitely. I set up the sheet to colour the relevant cells according to the words in
them for the 8 most common colours. Conditional Formatting was also used to show the li
of possible colours in the code. There is a cell which contains the number of colours
used, from 2 to 8, and is set by Data Validation to accept only integers between those t
values. Based on this cell, various formulas below decide whether to show their colour
not. Borders, by Conditional Formatting, only go around cells containing colours.

The macros…
There are various buttons, each of which performs a different operation called a Macro.
This is an automated procedure which can either be recorded directly by Excel or written
code behind the interface. Beyond what can be done on the screen, there are various oth
options available in the code editor, Visual Basic. In this game I have used IF functio
to check whether the code has been cracked at the end of each try, and a message box whi
pops up at the end. This box incorporates the value from a hidden cell elsewhere in the
workbook which counts the number of rows checked and returns the phrase shown in the 'We
Done' message. Some actions are very simple, such as Show or Hide Code, which merely ma
the first two rows visible or invisible. Others are rather more complicated; for instan
the button which prompts the generation of a random code types a formula from scratch in
each of the four cells which make up the code. The formula refers to more hidden cells
which use the ROUND and RAND functions along with the LOOKUP function to produce four
random numbers between 1 and the specified maximum, and then converts them to their rela
colours via a table. When the code cells give four random colours, they are copied and
pasted back in the same place using Paste Special to give only values. This leaves the
code as a straightforward written colour in each cell. The button for New Game deletes
code and the guesses along with the check number (a 1 entered into a corresponding cell
each row by each Check macro which enables the marker pegs to be shown). This leaves th
board ready for the next code.
The coding …
This is a copy of the coding for the sheet and the macros.     The actual code is in red an

Conditional    formatting for 8 conditions
Private Sub    Worksheet_Change(ByVal Target As Range)
Get to this    by right clicking on the Game sheet tab and going to View Code
Dim rng    As Range

Set rng = Intersect(Target, Range("I1:O1,I5:O23"))
If rng Is Nothing Then
Exit Sub
Else
Dim cl As Range
For Each cl In rng
Select Case cl.Text
This sets the relevant range - the cells which need to change colour depending on conten
Case ""
cl.Interior.ColorIndex = 15
cl.Font.ColorIndex = 15
Case "black"
cl.Interior.ColorIndex = 1
cl.Font.ColorIndex = 1
Case "white"
cl.Interior.ColorIndex = 2
cl.Font.ColorIndex = 2
Case "blue"
cl.Interior.ColorIndex = 5
cl.Font.ColorIndex = 5
Case "brown"
cl.Interior.ColorIndex = 12
cl.Font.ColorIndex = 12
Case "green"
cl.Interior.ColorIndex = 4
cl.Font.ColorIndex = 4
Case "red"
cl.Interior.ColorIndex = 3
cl.Font.ColorIndex = 3
Case "yellow"
cl.Interior.ColorIndex = 6
cl.Font.ColorIndex = 6
Case "orange"
cl.Interior.ColorIndex = 45
cl.Font.ColorIndex = 45
Case Else
Exit Sub
These show the colour indexes for each case. The code is located in the Change section,
it should refresh itself every time there is a change to the sheet just like normal
conditional formatting.
End Select
Next cl
End If

End Sub

Macros:
Macros:
s. This was achieved by listing the
f each used by both the code and the
rall score for the number of correct
and the guess did not, no marks would be
not, no marks were given. If both the
r of times the colour appeared in the
n the guess determined the final mark.
and three in the guess, the score would
ess the score would be 1. This gave the
ifferentiate between them. A direct
e the number of black pegs, and thus the

cell change to black or white depending
there are a maximum of three conditions
altering the code behind the sheet (I
xpands the number of conditions
levant cells according to the words in
ormatting was also used to show the list
which contains the number of colours
o accept only integers between those two
w decide whether to show their colour or
around cells containing colours.

a different operation called a Macro.
recorded directly by Excel or written in
e on the screen, there are various other
. In this game I have used IF functions
end of each try, and a message box which
ue from a hidden cell elsewhere in the
nd returns the phrase shown in the 'Well
as Show or Hide Code, which merely make
re rather more complicated; for instance,
m code types a formula from scratch into
e formula refers to more hidden cells
the LOOKUP function to produce four
, and then converts them to their related
r random colours, they are copied and
to give only values. This leaves the
ell. The button for New Game deletes the
a 1 entered into a corresponding cell for
ker pegs to be shown). This leaves the
macros.   The actual code is in red and

b and going to View Code

d to change colour depending on content

code is located in the Change section, so
nge to the sheet just like normal

To top