checking winning lotto numbers

B

Bubba32Grump

New Member
Joined
Apr 21, 2024
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Once I purchase a lotto ticket, I then enter my numbers into excel, once the lotto numbers have been drawn I would enter the winning numbers into my spread sheet. I want to highlight each winning number in a different fill color.
I can't seem to get it to work though, I've tried conditional formatting, as well as to see if first number in the array of my winning numbers, I've also tried to use the winning numbers are equal to my array.
Thanks for any help with this.
Mark
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Sort by date Sort by votes
If your Lotto numbers are in A2:A8 and the drawn numbers are in B2:B8, this should color your numbers green.

Code: 
Sub Check_Lotto()
Dim i As Long, j As Long
    For i = 2 To 8
        For j = 2 To 8
            If Cells(j, 1).Value = Cells(i, 2).Value Then Cells(j, 1).Interior.Color = RGB(0, 255, 0)
        Next j
    Next i
End Sub

or with different colors

Code: 
Sub Check_Lotto_B()
Dim colorArr, colRGB
Dim i As Long, j As Long
colorArr = Array("196, 215, 155", "222, 193, 218", "184, 204, 228", "248, 203, 173", "214, 227, 188", "234, 209, 220", "204, 192, 218")
    For i = 2 To 8
        For j = 2 To 8
            If Cells(j, 1).Value = Cells(i, 2).Value Then
                colRGB = Split(colorArr(i - 2), ",")
                Cells(j, 1).Interior.Color = RGB(Trim(colRGB(0)), Trim(colRGB(1)), Trim(colRGB(2)))
            End If
        Next j
    Next i
End Sub
 
Upvote 0
Sorry I'm not as advanced as you may have thought. No I haven't been able to make it work for me. I do appreciate your answer thought and hope to find the way to get it to run.
Thanks
Mark
 
Upvote 0
Another approach that you can try

Lotto.xlsm
ABCD
144
2311 
3201212
4122020
52823
6414141
7444444
81940
9
1c
Cell Formulas
RangeFormula
D1D1=SUM(COUNTIF(A2:A8,B2:B8))
C1C1=COUNT(XLOOKUP(A2:A8,B2:B8,A2:A8,"",0))
C2:C8C2=XLOOKUP(A2:A8,B2:B8,A2:A8,"",0)
Dynamic array formulas.
 
Upvote 0
Thanks again, this looks more like the language I was hoping for. I don't have time right now but will be attempting to get this working thanks again. I will let you know when I get it working.
Thanks
Mark
 
Upvote 0
Lotto.xlsm
ABC
14
2311 
3201212
4122020
52823 
6414141
7444444
81940 
9
1c
Cell Formulas
RangeFormula
C1C1=SUM(COUNTIF(A2:A8,B2:B8))
C2:C8C2=XLOOKUP(B2,$A$2:$A$8,$A$2:$A$8,0,0)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
C2:C8Expression=XLOOKUP(B2,$A$2:$A$8,$A$2:$A$8,0,0)>0textNO
 
Upvote 0
Solution
Lotto.xlsm
AB
1
2311
32012
41220
52823
64141
74444
81940
9
1c
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B2:B8Expression=OR(B2=$A$2:$A$8)textNO
 
Upvote 0
Hi Bubba32Grump

Here is how I would do for Lotto numbers record:

20240429 Excel template for lottery.xlsx
ABCDEFGHIJ
1Search7268Frequency of Winning
2Winning History2
3
4Total Wins2235678
5DateBuy NumberRemarkReturn if WIN1st Winner2nd Winner3rd WinnerRunner Up number1Runner Up number2Runner Up number3
61/4/20247268My door number0778855457000624939934634
71/4/20240717My Car number0778855457000624939934634
81/4/20242793His ID0778855457000624939934634
91/4/20244287Her Dog0778855457000624939934634
101/4/20240938My Shirt number0778855457000624939934634
112/4/20247268My door number19383688428933172683664
122/4/20240717My Car number09383688428933172683664
132/4/20242793His ID09383688428933172683664
142/4/20244287Her Dog09383688428933172683664
152/4/20240938My Shirt number19383688428933172683664
163/4/20247268My door number0211766998335343915749842
173/4/20240717My Car number0211766998335343915749842
183/4/20242793His ID0211766998335343915749842
193/4/20244287Her Dog0211766998335343915749842
203/4/20240938My Shirt number0211766998335343915749842
214/4/20247268My door number0853463559674211550481781
224/4/20240717My Car number0853463559674211550481781
234/4/20242793His ID0853463559674211550481781
244/4/20244287Her Dog0853463559674211550481781
254/4/20240938My Shirt number0853463559674211550481781
265/4/20247268My door number0525168757152892398886889
275/4/20240717My Car number0525168757152892398886889
285/4/20242793His ID0525168757152892398886889
295/4/20244287Her Dog0525168757152892398886889
305/4/20240938My Shirt number0525168757152892398886889
316/4/20247268My door number0712385299045753977537313
326/4/20240717My Car number0712385299045753977537313
336/4/20242793His ID0712385299045753977537313
346/4/20244287Her Dog0712385299045753977537313
356/4/20240938My Shirt number0712385299045753977537313
367/4/20247268My door number021908011987829918461804
377/4/20240717My Car number021908011987829918461804
387/4/20242793His ID021908011987829918461804
397/4/20244287Her Dog021908011987829918461804
407/4/20240938My Shirt number021908011987829918461804
41(continue entry… …)
BuyNumber
Cell Formulas
RangeFormula
E2E2=COUNTIF(Table1,C1)
D4D4=SUM(Table2[Return if WIN])
E4:F4E4=COLUMN(B4)
G4:J4G4=COLUMN(E4)
D6:D40D6=COUNTIFS(Table2[@[1st Winner]:[Runner Up number3]],[@[Buy Number]])
E6:J40E6=VLOOKUP($A6,Table1,E$4)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
D6:D40Cell Value>0.5textNO


20240429 Excel template for lottery.xlsx
ABCDEFGHIJKLMN
1
2Date1st Winner2nd Winner3rd WinnerRunner Up number1Runner Up number2Runner Up number3Runner Up number4Runner Up number5Runner Up number6Runner Up number7Runner Up number8Runner Up number9Runner Up number10
31/4/20247788554550227000624939934634241358869052723766861084
42/4/20240938368804284289033172683664238115487258948176820271
53/4/20242117669991028335343915749842288241775370477633943285
64/4/20248534635503689674211550481781752142689282421699753272
75/4/20245251687539027152892398886889413024014627588555615580
86/4/20247123852904539045753977537313534762793629501291767141
97/4/20242190801148409878299108461804178608303547071766751389
108/4/20248080922385819854535216825075732518128603071777939010
119/4/20244932165794285815792231484433700762222665496205706072
1210/4/20244580140838012439169737160320691059939913025170507529
1311/4/20249534740065806473435301517342629276496548025258657465
1412/4/20243652545746975106780953046221637048654145279391124099
1513/4/20240785656416808196907119064626747351413117992617101004
1614/4/20243954448780597690429310481489698753799399362178838120
1715/4/20247626956539398543919281006364652161457815128254930641
1816/4/20247675737445316914992068571280192725564609132108940340
1917/4/20243269176635662334415798107771139059205143416259648116
2018/4/20240309104470662846391686128277559141157127778242146711
2119/4/20242793666908348061722183095700876924404125169329168738
2220/4/20247469337906989208987510790080600441121441056545823729
2321/4/20246733273936654095399602336850584298134276610776347262
2422/4/20247088439771480284324672503640536690204870777015672542
2523/4/20245803477534628071258219218565372142879264557927818736
2624/4/20245497623212498113063060532825566942967338560002754389
2725/4/20243941846971735067784030759650506309958838862620187997
2826/4/20245416094764025983432722107620517912037849998333267058
2927/4/20244287924030155595617242523271375613917268020470924856
3028/4/20243105617324417967820566446784840432478774959468415417
3129/4/20244426590234538210225189471619563112695653023734956074
3230/4/20248010770306764175210242043704017481535424955182860666
33
WinNumber
Cells with Conditional Formatting
CellConditionCell FormatStop If True
3:32Cell ValueduplicatestextNO
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question Question
Excel/365 VBA crash issues recently
Replies
0
Views
532
picklefactory
P
M
  • Solved
Alternative to COUNTIFS for a constantly growing database
Replies
2
Views
532
multifidus
M
J
  • Solved
Search Multiple Columns for One Value
2
Replies
16
Views
961
Fluff
Fluff
L
  • Solved
Formatting cells filled by array formulae
Replies
5
Views
219
Ludwig
L
Nlhicks
  • Solved
Formula to find a ticket number in a list of winning tickets
Replies
6
Views
503
DanteAmor
DanteAmor

Forum statistics

Threads
1,224,824
Messages
6,181,186
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top