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.
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>Conditional formatting from the menubar
In the condition1 box select Formula is and enter the following formula
(where A5 is the selected cell)
Add your format (i.e. highlight cell background yellow)
In the condition2 box repeat the process, with a different format, change the formula to read
(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.
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.
In my file, from the menubar choose
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
They are basically referring to the Named ranges above
Basically, this can be broken down like follows
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
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)
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,