How To Highlight Lottery Numbers In An Excel Spreadsheet Using VBA

When lottery researchers want to test a theory, it’s a good idea to so some testing on some real data. A typical scenario is to find occurrences of specific numbers in recent results.

This article explains how to use VBA to accurately highlight specific numbers over recent draws.

Creating The Initial Parameters For The VBA Program

We’ll need to establish three main factors.

  • The initial data set of results
  • Where the analysis should start from
  • The list of numbers to look for

The data should look something like this:


8 19 26 29 30 33
2 9 12 22 35 39
6 9 12 19 20 31
5 16 20 22 27 29
18 20 35 15 31 2
3 19 20 27 31 35

Next, we need to consider the subset of data to analyse; in this example we’ll take the last four lines. Finally, we need to define the numbers the code should look for.

Creating The VBA Code And Highlighting The Selected Numbers

First, we’ll move to where the data is held and create a range. The range is just the data that we want the code to move through. We’ll remove the formatting so our code starts again from scratch each time we run it.


Dim rng As Range
Worksheets(1).Activate
Range("a1").Activate
Cells.ClearFormats

Next, we’ll tell the VBA code how many rows from the bottom we want to use; we’re telling it to look at the last 4 draws.

Also, we’ll define the paremeters of the data, the number of columns and rows. This will help us create the data range.


myRows = 4

allRows = ActiveCell.End(xlDown).Row
allcols = ActiveCell.End(xlToRight).Column
startRow = allRows - myRows + 1

Now we can actually select the data. If you were to end the program here, you’d see the last four rows of data selected by the cursor.


add1 = Range("a" & startRow).Address
add2 = ActiveCell.Offset(allRows - 1, allcols - 1).Address
Set rng = Range(add1, add2)
rng.Select

With our data selected, we need to define our selected numbers, run through the selection and highlight any numbers found.


myNumbers = ",5,27,"

For x = 1 To rng.Rows.Count

For y = 1 To rng.Columns.Count

testNo = "," & rng.Cells(x, y) & ","

If InStr(myNumbers, testNo) > 0 Then

rng.Cells(x, y).Font.Bold = True

End If

Next
Next

The code above is referred to as a nested loop; it’s going through the data row by row and column by column to find the numbers. In this example any numbers found are changed to a bold font.

One important point is for the code to identify the actual number and not confuse numerals such as 5 and 35. That’s why the search code adds a comma around the numbers so it searches for “,5,” and not just “5”.

Summary

Although Excel has its own conditional formatting tools, for lotto research the standard tools won’t really do the job, especially for more complex ideas and theories. This is where a little knowledge of VBA can take your lotto research to new heights.