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 * * *





"DB" wrote in message
k...
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" wrote in message
k...
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" wrote in message
...
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" wrote in message
k...
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 :)


"PCLIVE" wrote in message
...
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" wrote in message
. ..
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 :)