Comparing numbers in cells and highlighting matching numbers

  • Thread starter Thread starter Guest
  • Start date Start date
G

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,
 
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.
 
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
 
Back
Top