Got it!

"DB" wrote in message
k...
i tried - did you get this one ok

Dave :)


"PCLIVE" wrote in message
...
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" wrote in message
...
That email address is old. Try the one in this response.

"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 :)