Comparing numbers in cells and highlighting matching numbers

[Guest]

We have a lottery syndicate at work with fixed numbers that are put on every
week (6 from 49 with 12 rows) These numbers never change so I can put them
into 12 fixed rows. Each week I want to key in the 6 numbers that have been
drawn and if there is a match in any of the rows for each of the 6 individual
numbers I want to highlight the corresponding number in the fixed row, say
blue. If all off the numbers in a row come up (most unlikely) I would want
all of the numbers to flash red and have a sound sample play. I have looked
at the conditional formatting but it will not do it for the amount of numbers
we have. Can anyone suggest a way to do this?


Thanks in advance,

 

[Myrna Larson]

Are you saying you have selected just 12 combinations out of the total of
13,983,816 that are possible?

Let's say you put the sets of interest in A2:F13, with a single number in each
cell.

Then in A1:F1 we'll put the winners for the week

In G2, type the following formula:

=SUMPRODUCT(--ISNUMBER(MATCH($A2:$F2,$A$1:$F$1,0)))

and copy down through G13.

Then set up the Conditional formatting: select A2:F13, with A2 the active
cell, use this for the formula for 1st condition:

=$G2=6 and set the background to Red

(you can't make it flash, thank heavens!)

Use this formula for the 2nd condition:

=ISNUMBER(MATCH(A2,$A$1:$F$1,0)) and set the background to Blue.

To play a sound you could use a Worksheet_Calculate event macro that is
triggered by the value of 6 appearing in column G.

 

[Guest]

Myrna,

Thanks for that it works great, I obviously had to alter the references to
suit, but it will make it easier for the guy who looks after our lottery
syndicate. I will be able to get some more work out of him on a Monday
morning.

Daedalus1