Search code examples
excelvbaautomation

Check off tabularized lotto numbers automatically

There is a fundraising lotto in our local pub whereby people pick 6 lotto numbers, and after each Saturday draw any matching numbers are highlighted. This is repeated each week until someone has all 6 of their numbers highlighted and they win the prizemoney.

I have a list of 50 people's lotto numbers in a spreadsheet. Separately, I have each week's lotto numbers in a different table.

I currently go through highlighting them manually. I know I could write a vba script with a loop within a loop within a loop checking each person's number with each result number but that seems very clunky. I can't for the life of me relate a conditional formatting formula applying to each individual number.

Any advice for automation would be gratefully received.

Solution

  • I had a slightly different interpretation of your question. To see how it might work out, I put the week number into a cell and used this formula in conditional formatting to see how the results would look after n weeks:

    =COUNTIF(INDIRECT("J2:O"&$H$2+1),A2)

    So after 4 weeks nobody has won, but after 5 weeks the person in row 19 has won:

    enter image description here

    enter image description here

    If you were adding results week by week, you could just use a big enough range to include all the results e.g.

    =COUNTIF($J$2:$O$20,A2)