Can Excel sort my lottery numbers?

Lydokane · 11-01-2009, 08:18 AM

I am using Excel 2003.

At work we are running a lottery pool. There are 22 of us who buy a $5 ticket twice a week. We all use the same numbers with every purchase. I would like to make a worksheet (and/or a macro) that makes it easy for me to verify if any tickets have a cash value. My plan was to use cells A3:E112 to record all of our picks and use cells A1:A5 to record each drawing. When I input the numbers for the drawing I want Excel to highlight any matching numbers from our picks. I know conditional formatting will not work because you only get three conditions. I am guessing that a macro is necessary. I appreciate any help somebody could give me.

Thanks,

Lydokane

**Glaswegian** · 11-01-2009, 08:32 AM

Hi Lydokane

This is some code I've been using for several years and for a variety of uses. It matches cells in the first range against cells in the second range. The ranges do not need to be of equal size. When you run the code an input box will appear - use the mouse to select the first range, click OK and repeat for the second range. If there is a match then the cells in the second range turn green - if there is no match then the cells in the first range turn red.

Something I noticed was that the two ranges you have listed seem to overlap - I'm assuming this was just a typo...

Code:

Sub FindDuplicates()
Dim rng1 As Range
Dim rng2 As Range
Dim bMatch As Boolean
Dim origRng As Range
Dim compRng As Range

On Error Resume Next
Set origRng = Application.InputBox("Choose the first range", "Range 1", Type:=8)
    If origRng Is Nothing Then Exit Sub
Set compRng = Application.InputBox("Choose the second range", "Range 2", Type:=8)
'matches first cell in first range against each cell in second range
'ranges do not need to be equal size
'if there is a match then cell in second range turns green
'if there is not a  match then cell in first range turns red
    For Each rng1 In origRng
        bMatch = False
        For Each rng2 In compRng
            If rng1 = rng2 Then
                bMatch = True
                rng2.Interior.ColorIndex = 4
            End If
        Next rng2
            If bMatch = False Then
                rng1.Interior.ColorIndex = 3
            End If
    Next rng1
End Sub

11-01-2009, 08:18 AM	#1 (permalink)
Lydokane Tech, Microsoft Support Join Date: May 2005 Location: Michigan City Posts: 480 OS: Win XP Pro SP2	Can Excel sort my lottery numbers? I am using Excel 2003. At work we are running a lottery pool. There are 22 of us who buy a $5 ticket twice a week. We all use the same numbers with every purchase. I would like to make a worksheet (and/or a macro) that makes it easy for me to verify if any tickets have a cash value. My plan was to use cells A3:E112 to record all of our picks and use cells A1:A5 to record each drawing. When I input the numbers for the drawing I want Excel to highlight any matching numbers from our picks. I know conditional formatting will not work because you only get three conditions. I am guessing that a macro is necessary. I appreciate any help somebody could give me. Thanks, Lydokane __________________

11-01-2009, 08:32 AM	#2 (permalink)
Glaswegian Moderator/ Rangemaster TSF Academy; Analyst, Security Team; Oor Wullie; TSF Surgeon and Resident Comic Join Date: Sep 2005 Location: Glasgow Posts: 25,499 OS: Win XP Pro SP3 / Win 7 Pro My System Blog Entries: 10	Re: Can Excel sort my lottery numbers? Hi Lydokane This is some code I've been using for several years and for a variety of uses. It matches cells in the first range against cells in the second range. The ranges do not need to be of equal size. When you run the code an input box will appear - use the mouse to select the first range, click OK and repeat for the second range. If there is a match then the cells in the second range turn green - if there is no match then the cells in the first range turn red. Something I noticed was that the two ranges you have listed seem to overlap - I'm assuming this was just a typo... Code: Sub FindDuplicates() Dim rng1 As Range Dim rng2 As Range Dim bMatch As Boolean Dim origRng As Range Dim compRng As Range On Error Resume Next Set origRng = Application.InputBox("Choose the first range", "Range 1", Type:=8) If origRng Is Nothing Then Exit Sub Set compRng = Application.InputBox("Choose the second range", "Range 2", Type:=8) 'matches first cell in first range against each cell in second range 'ranges do not need to be equal size 'if there is a match then cell in second range turns green 'if there is not a match then cell in first range turns red For Each rng1 In origRng bMatch = False For Each rng2 In compRng If rng1 = rng2 Then bMatch = True rng2.Interior.ColorIndex = 4 End If Next rng2 If bMatch = False Then rng1.Interior.ColorIndex = 3 End If Next rng1 End Sub __________________ Iain - Defender of the Haggis and all things Scottish. I don't help by PM - post in the Forums. PC Safety & Security::PC running a bit slow?::Donate::Photographers Corner