[Solved] need help with Excel

  • 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
    =NOT(ISNA(MATCH(A5,rngNos,0)))
    (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
    =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>Name>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!