How to highlight a matching cell from a set of results for a lottery spreadsheet

J

johno17

New Member
Joined
Jun 9, 2008
Messages
5
Hi, I am trying to set up a lottery spreadsheet, where I enter the results and matching numbers would be highlighted
Any help would be appreciated

Thanks in advanced
 
Sort by date Sort by votes
johno17,

Barry's formula (with my formatting) will do this automatically.

Excel Workbook
CDEFGHI
2Results789254149
3
4
5Lottery Numbers
6
7159493115
824232548493
91314294115
1014157817
1161626364647
12246212530
13711914308
14369121316
154546481234
1625161921843
174213184547
184818263036
19242527293142
2051015202530
21278111618
223425273031
23111640432115
24222329364041
Sheet1



Or, if you are looking for a macro(s):

Before the macro:

Excel Workbook
CDEFGHI
2Results789254149
3
4
5Lottery Numbers
6
7159493115
824232548493
91314294115
1014157817
1161626364647
12246212530
13711914308
14369121316
154546481234
1625161921843
174213184547
184818263036
19242527293142
2051015202530
21278111618
223425273031
23111640432115
24222329364041
Sheet1



After the macro:

Excel Workbook
CDEFGHI
2Results789254149
3
4
5Lottery Numbers
6
7159493115
824232548493
91314294115
1014157817
1161626364647
12246212530
13711914308
14369121316
154546481234
1625161921843
174213184547
184818263036
19242527293142
2051015202530
21278111618
223425273031
23111640432115
24222329364041
Sheet1



There are two macros, one to "HighlightMatch", and one to clear the match "MatchClear".

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Press and hold down the 'ALT' key, and press the 'F11' key.

On the 'Insert' menu, click 'Module'.

Copy the below code, and paste it into the Module.

Code: 
Option Explicit
Sub HighlightMatch()
    Dim c
    Application.ScreenUpdating = False
    With Range("D7:I24")
        .Font.FontStyle = "Regular"
        .Interior.ColorIndex = xlNone
    End With
    For Each c In Range("D7:I24").Cells
        If c.Value = [D2] Or c.Value = [E2] Or c.Value = [F2] Or c.Value = [G2] Or c.Value = [H2] Or c.Value = [I2] Then
            With c
                .Font.FontStyle = "Bold"
                .Interior.ColorIndex = 3
            End With
        End If
    Next
    Application.ScreenUpdating = True
End Sub

Sub MatchClear()
    Application.ScreenUpdating = False
    With Range("D7:I24")
        .Font.FontStyle = "Regular"
        .Interior.ColorIndex = xlNone
    End With
    Application.ScreenUpdating = True
End Sub


Then run the "HighlightMatch" or the "MatchClear" macro.


Have a great day,
Stan
 
Upvote 0

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
So in row 13, we have three matches (7, 9, and 8) without caring about the order they are in, right? In that case, the formula to count up the matches in row D would be: =SUM(COUNTIF(D$2:I$2,D7:I7)*1) confirmed with Control+Shift+Enter
 
Last edited:
Upvote 0
The other less painful way is to do the lottery online, they email you when you WIN, no nasty checking of tickets ;0)
 
Upvote 0
Hi, thanks for the help so far, but the totals for matches are not adding up, any more help

Thanks
<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD><TD>J</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD>BONUS</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD>Results</TD><TD style="TEXT-ALIGN: right">7</TD><TD style="TEXT-ALIGN: right">10</TD><TD style="TEXT-ALIGN: right">9</TD><TD style="TEXT-ALIGN: right">15</TD><TD style="TEXT-ALIGN: right">30</TD><TD style="TEXT-ALIGN: right">40</TD><TD style="TEXT-ALIGN: right">3</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD> </TD><TD> </TD><TD>Lottery Numbers</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD>matches</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">5</TD><TD style="BACKGROUND-COLOR: #ff0000; TEXT-ALIGN: right">9</TD><TD style="TEXT-ALIGN: right">49</TD><TD style="TEXT-ALIGN: right">31</TD><TD style="BACKGROUND-COLOR: #ff0000; TEXT-ALIGN: right">15</TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">24</TD><TD style="TEXT-ALIGN: right">23</TD><TD style="TEXT-ALIGN: right">25</TD><TD style="TEXT-ALIGN: right">48</TD><TD style="TEXT-ALIGN: right">49</TD><TD style="BACKGROUND-COLOR: #0000ff; TEXT-ALIGN: right">3</TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">13</TD><TD style="TEXT-ALIGN: right">14</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="BACKGROUND-COLOR: #ff0000; TEXT-ALIGN: right">9</TD><TD style="TEXT-ALIGN: right">41</TD><TD style="BACKGROUND-COLOR: #ff0000; TEXT-ALIGN: right">15</TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">14</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">5</TD><TD style="BACKGROUND-COLOR: #ff0000; TEXT-ALIGN: right">7</TD><TD style="TEXT-ALIGN: right">8</TD><TD style="TEXT-ALIGN: right">17</TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">6</TD><TD style="TEXT-ALIGN: right">16</TD><TD style="TEXT-ALIGN: right">26</TD><TD style="TEXT-ALIGN: right">36</TD><TD style="TEXT-ALIGN: right">46</TD><TD style="TEXT-ALIGN: right">47</TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">12</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">4</TD><TD style="TEXT-ALIGN: right">6</TD><TD style="TEXT-ALIGN: right">21</TD><TD style="TEXT-ALIGN: right">25</TD><TD style="BACKGROUND-COLOR: #ff0000; TEXT-ALIGN: right">30</TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">13</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="BACKGROUND-COLOR: #ff0000; TEXT-ALIGN: right">7</TD><TD style="TEXT-ALIGN: right">11</TD><TD style="BACKGROUND-COLOR: #ff0000; TEXT-ALIGN: right">9</TD><TD style="TEXT-ALIGN: right">14</TD><TD style="BACKGROUND-COLOR: #ff0000; TEXT-ALIGN: right">30</TD><TD style="TEXT-ALIGN: right">8</TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">14</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="BACKGROUND-COLOR: #0000ff; TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">6</TD><TD style="BACKGROUND-COLOR: #ff0000; TEXT-ALIGN: right">9</TD><TD style="TEXT-ALIGN: right">12</TD><TD style="TEXT-ALIGN: right">13</TD><TD style="TEXT-ALIGN: right">16</TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">15</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">45</TD><TD style="TEXT-ALIGN: right">46</TD><TD style="TEXT-ALIGN: right">48</TD><TD style="TEXT-ALIGN: right">12</TD><TD style="BACKGROUND-COLOR: #0000ff; TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">4</TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">16</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">25</TD><TD style="TEXT-ALIGN: right">16</TD><TD style="TEXT-ALIGN: right">19</TD><TD style="TEXT-ALIGN: right">21</TD><TD style="TEXT-ALIGN: right">8</TD><TD style="TEXT-ALIGN: right">43</TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">17</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">42</TD><TD style="TEXT-ALIGN: right">13</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">8</TD><TD style="TEXT-ALIGN: right">45</TD><TD style="TEXT-ALIGN: right">47</TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">18</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">4</TD><TD style="TEXT-ALIGN: right">8</TD><TD style="TEXT-ALIGN: right">18</TD><TD style="TEXT-ALIGN: right">26</TD><TD style="BACKGROUND-COLOR: #ff0000; TEXT-ALIGN: right">30</TD><TD style="TEXT-ALIGN: right">36</TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">19</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">24</TD><TD style="TEXT-ALIGN: right">25</TD><TD style="TEXT-ALIGN: right">27</TD><TD style="TEXT-ALIGN: right">29</TD><TD style="TEXT-ALIGN: right">31</TD><TD style="TEXT-ALIGN: right">42</TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">20</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">5</TD><TD style="BACKGROUND-COLOR: #ff0000; TEXT-ALIGN: right">10</TD><TD style="BACKGROUND-COLOR: #ff0000; TEXT-ALIGN: right">15</TD><TD style="TEXT-ALIGN: right">20</TD><TD style="TEXT-ALIGN: right">25</TD><TD style="BACKGROUND-COLOR: #ff0000; TEXT-ALIGN: right">30</TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">21</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="BACKGROUND-COLOR: #ff0000; TEXT-ALIGN: right">7</TD><TD style="TEXT-ALIGN: right">8</TD><TD style="TEXT-ALIGN: right">11</TD><TD style="TEXT-ALIGN: right">16</TD><TD style="TEXT-ALIGN: right">18</TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">22</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="BACKGROUND-COLOR: #0000ff; TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">4</TD><TD style="TEXT-ALIGN: right">25</TD><TD style="TEXT-ALIGN: right">27</TD><TD style="BACKGROUND-COLOR: #ff0000; TEXT-ALIGN: right">30</TD><TD style="TEXT-ALIGN: right">31</TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">23</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">11</TD><TD style="TEXT-ALIGN: right">16</TD><TD style="BACKGROUND-COLOR: #ff0000; TEXT-ALIGN: right">40</TD><TD style="TEXT-ALIGN: right">43</TD><TD style="TEXT-ALIGN: right">21</TD><TD style="BACKGROUND-COLOR: #ff0000; TEXT-ALIGN: right">15</TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">24</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">22</TD><TD style="TEXT-ALIGN: right">23</TD><TD style="TEXT-ALIGN: right">29</TD><TD style="TEXT-ALIGN: right">36</TD><TD style="BACKGROUND-COLOR: #ff0000; TEXT-ALIGN: right">40</TD><TD style="TEXT-ALIGN: right">41</TD><TD> </TD></TR></TBODY></TABLE>
<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR><TD>C7</TD><TD>{=SUM((D7:I7=$D$2:$I$2)*1)}</TD></TR><TR><TD>C8</TD><TD>{=SUM((D8:I8=$D$2:$I$2)*1)}</TD></TR><TR><TD>C9</TD><TD>{=SUM((D9:I9=$D$2:$I$2)*1)}</TD></TR><TR><TD>C10</TD><TD>{=SUM((D10:I10=$D$2:$I$2)*1)}</TD></TR><TR><TD>C11</TD><TD>{=SUM((D11:I11=$D$2:$I$2)*1)}</TD></TR><TR><TD>C12</TD><TD>{=SUM((D12:I12=$D$2:$I$2)*1)}</TD></TR><TR><TD>C13</TD><TD>{=SUM((D13:I13=$D$2:$I$2)*1)}</TD></TR><TR><TD>C14</TD><TD>{=SUM((D14:I14=$D$2:$I$2)*1)}</TD></TR><TR><TD>C15</TD><TD>{=SUM((D15:I15=$D$2:$I$2)*1)}</TD></TR><TR><TD>C16</TD><TD>{=SUM((D16:I16=$D$2:$I$2)*1)}</TD></TR><TR><TD>C17</TD><TD>{=SUM((D17:I17=$D$2:$I$2)*1)}</TD></TR><TR><TD>C18</TD><TD>{=SUM((D18:I18=$D$2:$I$2)*1)}</TD></TR><TR><TD>C19</TD><TD>{=SUM((D19:I19=$D$2:$I$2)*1)}</TD></TR><TR><TD>C20</TD><TD>{=SUM((D20:I20=$D$2:$I$2)*1)}</TD></TR><TR><TD>C21</TD><TD>{=SUM((D21:I21=$D$2:$I$2)*1)}</TD></TR><TR><TD>C22</TD><TD>{=SUM((D22:I22=$D$2:$I$2)*1)}</TD></TR><TR><TD>C23</TD><TD>{=SUM((D23:I23=$D$2:$I$2)*1)}</TD></TR><TR><TD>C24</TD><TD>{=SUM((D24:I24=$D$2:$I$2)*1)}</TD></TR></TBODY></TABLE></TD></TR><TR><TD>Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!</TD></TR></TBODY></TABLE>
 
Upvote 0
That formula is only counting numbers which match within the corresponding column. You can use gardnertoo's last suggested formula....although I prefer to use SUMPRODUCT to avoid CSE, i.e. in C7 copied down

=SUMPRODUCT(COUNTIF(D$2:I$2,D7:I7))

Edit: I'm assuming the count doesn't include the bonus ball.......
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
Conditional Formatting Help Needed!
2
Replies
15
Views
179
coulombevin
coulombevin
ChrisMac1
  • Solved
Highlight cells
Replies
3
Views
110
etaf
etaf
R
  • Question Question
Match value where same value occurs multiple times
Replies
6
Views
210
Rijnsent
Rijnsent
C
  • Question Question
VLookup using a row number
Replies
1
Views
78
Joe4
Joe4
B
  • Question Question
Highlight Column When Cells In Range Are Active
Replies
5
Views
145
mumps
mumps

Forum statistics

Threads
1,225,927
Messages
6,187,878
Members
453,445
Latest member
kennylee

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