Is this possible ???

[DB]

My local pub runs a competition based on the National Lottery.....and the
method they are using takes ages each week.

The punter picks 6 numbers and when the lottery draw takes place each week -
any numbers that a punter has chosen are crossed off, the first punter to
have their 6 numbers crossed off wins the pot.

The pot is always won about every 8 weeks so my idea was to say have a
section at the tops of the chart to input each weeks 6 numbers.

Then below i would type in every person 6 numbers - but the clever bit I was
hoping to try and get Excel to do , would be when I enter the 6 numbers for
that week I would like it to check everyones selections and if it matches
then it would "colour" the box, or some method to indicate that it has been
chosen.



Is this possible or is what i'm asking a bit to much effort.

Many thanks for your time and help.

Dave

 

[Guest]

You can Conditional Formatting:

Assume "lottery" draw numbers are in Cells B1 to G1

For each column select row 2 for as many players as required, then go to
Format==>Conditional Formatting==>Formula is: =B2=$B$1 and set format to
required colour.

Repeat for each column i.e =C2=$C$1 etc

HTH

 

[PCLIVE]

Try using a macro.

The first line finds the last cell with data in column A. This is assumed
to be the column that will include the first of six selected numbers by a
punter. The numbers should be entered across. This code assumes that the
lottery drawn numbers are entered across the top of the spreadsheet starting
at A1 and working over through F1. This code assumes that your punters'
numbers start at row 7. You can adjust as necessary. If a punter's number
matches any of the numbers entered across the top, then this code colors
that cell Green. You can adjust the color. Hope you find this useful.

Sub test()
lr = Range("A65536").End(xlUp).Row

For i = 1 To 6
For Each cell In Range("A7:F" & lr)
'If n = 7 Then End
If cell.Value = Cells(1, i).Value _
Then
With cell.Interior
.ColorIndex = 4
.Pattern = xlSolid
End With
Else:
End If
Next cell
Next i

End Sub

 

[DB]

Thanks for the info........

I'm not super experienced with Excel and this looks a bit complicated.

I'll give it a test run tomorrow though.

I will use a1 to f1
then week 2 will be b2 to f2
week 3 a3 to f3


will this still work.


Dave

 

[Guest]

Dave,
In my "solution" Row 1 will contain the numbers to be
matched. Rows 2 onwards will contain the selection of each person in the
lottery, so if there are 50 people, rows 2 to 51 will contain their numbers.

Select rows 2 to 51 (or more than 51 if you want) for 1st column and enter
the Data Validation data; repeat for other columns.

If you want to enter several weeks selection, then it will get a bit
complicated and the VBA solution offered may be better.

Good luck!

 

[PCLIVE]

This should work fine. But I need to know where you will be putting your
punter's numbers and where you will be entering the Lottering Drawing
numbers. This updated code uses A1 to F1 for the weekly drawings. You
enter the numbers in those cells and then the code below will test those
numbers against all of the numbers chosen by your punters. This code
assumes that your punters' numbers start at A7 to F7. If you tell me both
where you want your punters' numbers to start and where you want the weekly
draws to be entered, I can modify the code as necessary. As it is written,
enter your punter's numbers starting at A7 to F7, then A8 to F8, then A9 to
F9, and so on. Each week when the numbers are drawn, enter those numbers
from A1 to A7. Then run this code. The code checks each lottery drawn
number against your punters' numbers. If there is a match, the punters
number cell is colored green. Once it has checked all the numbers, the
weekly drawn numbers are copied over to columns L to Q starting at L2 to Q2.
This code also adds the label of "Week " and the appropriate week number in
Column K. Try it out. I think you'll like it.

To apply this code:
Press Alt + F11 to open the VB editor. Then right-click on the left-hand
pane (where it shows Sheet1, Sheet2, Sheet3), goto 'Insert' and then click
'Module'. Then paste the copy and paste the code below. Copy from "Sub"
all the way to "End Sub". You can now access this macro from the following
Excel menus. Tools-Macro-Macro. Then you can click on LotteryNumberChecker
and select 'Run'. If you want to create a shortcut key to run this code at
any time, click on 'LotteryNumberChecker', select 'Options', and then just
choose your desired shortcut key. Let me know if you have any questions.
Good luck.

Sub LotteryNumberChecker()
lr = Range("A65536").End(xlUp).Row

For i = 1 To 6
For Each Cell In Range("A7:F" & lr)
If Cell.Value = Cells(1, i).Value _
Then
With Cell.Interior
.ColorIndex = 4
.Pattern = xlSolid
End With
Range("H" & Cell.Row).Value = _
Range("H" & Cell.Row).Value + 1
Else:
End If
Next Cell
Next i

Range("A1:F1").Copy
Range("L65536").End(xlUp).Offset(1, 0).Select
r = Selection.Row
ActiveSheet.Paste
Range("K" & r).Value = "Week " & r - 1
Range("A1:F1").ClearContents

End Sub

 

[DB]

I tried emailing direct but not sure if it went..







Hi PCLIVE and many many thanks for your help.

I've put the macro in but im not sure what to do next.

I've a version that I put together to try the theory out - can I send you it
to have a look at?

It has all the things in roughly the correct place and is quite easy on the
eye too and printing it out each week would be very easy too.

Dave

 

[PCLIVE]

That email address is old. Try the one in this response.

 

[PCLIVE]

Ok that lst one still has the old email address. This one should be
updated. You can reply to me directly on this one.

 

[DB]

Thanks again.....the new one is great.....I tried to send my worksheets but
it got returned......I'll try again and see if you get mine.

Cheers]]Dave


PCLIVE

 

[DB]

i tried - did you get this one ok

Dave

Ok that lst one still has the old email address. This one should be
updated. You can reply to me directly on this one.

 

[PCLIVE]

Got it!

i tried - did you get this one ok

Dave

 

[DB]

PCLIVE is a Top Bloke.....

Not only has he helped me with info - but he has completely written a
worksheet that does everything that we needed at our local.

* * * TOP BLOKE * * *

Thanks again.....the new one is great.....I tried to send my worksheets
but it got returned......I'll try again and see if you get mine.

Cheers]]Dave


PCLIVE

I tried emailing direct but not sure if it went..







Hi PCLIVE and many many thanks for your help.

I've put the macro in but im not sure what to do next.

I've a version that I put together to try the theory out - can I send you
it to have a look at?

It has all the things in roughly the correct place and is quite easy on
the eye too and printing it out each week would be very easy too.

Dave