# [Solved] need help with Excel

• Hi
I have put my lotto numbers into Excel and I have eight squares for the weeks numbers that come out. What I want to do is highlight my numbers according to draw numbers. Can this be done and how? I have put a site up with my my sheet so it would make reading this question easyier.

http://members.optushome.com.au/sjcgreen/lottochecker.htm

• Use Conditional formatting.

Let your 6 regular numbers be in A2:F2
Name this range rngNos

Let your 2 Supplementary numbers be in G2:H2
Name this range rngSupps

Select a cell with a lotto number in that you want to apply a special format to

Select Format&gt;Conditional formatting from the menubar

In the condition1 box select Formula is and enter the following formula
=NOT(ISNA(MATCH(A5,rngNos,0)))
(where A5 is the selected cell)
In the condition2 box repeat the process, with a different format, change the formula to read
=NOT(ISNA(MATCH(A5,rngSupps,0)))
(I used bright green...)

Now, copy just the formats to all the other cells in the range where you enter your numbers... normal number matches will be highlighted yellow, those that match the Supp numbers will turn green..

Hope this helps - I have emailed you a sample file.

Will

• Hi
Thanks very much for your help I think I'm almost there. I have added the Conditional Format like you said but changed the A5 to C8 like it is on my sheet but how does the format know what the nos and supps are? on my sheet now my lotto numbers are and green.

Regards Steve

• In my file, from the menubar choose

Insert&gt;Name&gt;Define

You will see that there are two named ranges

rngNos which referes to =lotto!\$A\$2:\$F\$2
rngSupps which refers to =lotto!\$G\$2:\$H\$2

You will notice that the formulas in the conditional formatting are as follows

=NOT(ISNA(MATCH(A5,rngNos,0)))
and
=NOT(ISNA(MATCH(A5,rngSupps,0)))

They are basically referring to the Named ranges above
Basically, this can be broken down like follows

=MATCH(A5,rngNos,0)

If the value in A5 matches a value in rngNos (i.e. A2:F2), then it will return a number
If it does not find a match, the MATCH formula returns #NA

Then we build in ISNA function

=ISNA(MATCH(A5,rngNos,0)

This returns FALSE if a match is found (because the result is not #NA)
and TRUE if no match is found

by building in the NOT function we are effectively reversing the result (like a double negative)

so =NOT(ISNA(MATCH(A5,rngNos,0)))

returns FALSE if the value in A5 does NOT match the values in A2:F2
returns TRUE if it finds a match

By default, the conditional formatting is applied whenever the result of the formula is TRUE....

Hope this helps,

Will

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!