+ Reply to Thread
Results 1 to 39 of 39

Highlighting if a range of cells meet a value

  1. #1
    Registered User
    Join Date
    01-18-2008
    Posts
    24

    Highlighting if a range of cells meet a value

    Hi again,

    I have what I think is a simple question. I have tried, I think I was close then seemed to get futher away from an answer lol. I have a worksheet 200+ rows with 7 columns (euro lottery numbers) I had great help last night to highlight any matching numbers. I'm getting lazy now, can anyone help with a formula to highlight a cell in an adjacent column when 2 or more numbers in the corresponding cells match the winning numbers, I know all I have to do is look down the columns and check but if a simple formula could be inserted it would save me perhaps missing something.

    Thanks again

  2. #2
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    Let's get you working then

    See if link helps

    http://www.contextures.com/xlCondFor...html#Duplicate

    VBA Noob
    _________________________________________


    Credo Elvem ipsum etian vivere
    _________________________________________
    A message for cross posters

    Please remember to wrap code.

    Forum Rules

    Please add to your signature if you found this link helpful. Excel links !!!

  3. #3
    Registered User
    Join Date
    01-18-2008
    Posts
    24

    Getting there

    Hi thanks, there's a lot of useful info there. I have the cells highlighting when they match the inputted numbers (like the lottery example shown ) but still cant get an adjacent cell to highlight when 2 or more cells in a row match the winning numbers.

  4. #4
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675
    If the winning numbers are in A1:G1 and a pick in A3:G3 then to format H3 if 2 or more match try conditional formatting with this formula

    =SUM(COUNTIF(A$1:G$1,A3:G3))>1

  5. #5
    Registered User
    Join Date
    01-18-2008
    Posts
    24

    aghhhh

    Thanks daddylonglegs. I don't doubt that the formula is correct it's just me lol. please see attached screen capture of said sheet with the false retun showing. I have substituted my cell numbers for the ones you gave but as you can see although 3 numbers match it's showing false.

    Help please.

    EXAMPLE.jpg

  6. #6
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675
    I assumed that you were using the formula within conditional formatting. If you use the suggested formula on the worksheet then, as an array formula, it needs to be confirmed with CTRL+SHIFT+ENTER......or use this alternative with SUMPRODUCT to avoid CSE

    =SUMPRODUCT(COUNTIF(M$5:S$5,B4:H4))>1

  7. #7
    Registered User
    Join Date
    01-18-2008
    Posts
    24

    yehaaa

    thanks daddy ...................... and one more can the cell colour for a false return be different than that for a true return ? Promise I'll take what I have and practice with it lol

  8. #8
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675
    Normally you would just use conditional formatting for the TRUE condition and the other cells assume the default, of course normally this is just unformatted but you could format the column first with one colour e.g. blue and then use CF to give red when TRUE,

  9. #9
    Registered User
    Join Date
    01-18-2008
    Posts
    24

    hEY WHAT DO YA KNOW

    Hey guys, guess what DUH it was easy just used the cell value is equal to "false" then format.

    thanks again guys

  10. #10
    Registered User
    Join Date
    01-18-2008
    Posts
    24

    hmm

    Can the words "TRUE" and "FALSE" be substituted with "WIN" ans "LOSE" ?

  11. #11
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898
    Using formula supplied, you would enclose it in an IF statement...as such:

    Please Login or Register  to view this content.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  12. #12
    Registered User
    Join Date
    01-18-2008
    Posts
    24

    Hmmm

    Well guys, pride cometh before a fall and all that. I thought I had it but as you can see in the attachment it does shown win for three matching numbers but it also shows win when 2 numbers are the same. Think this is a small glitch am I right. I'm using the following formula.

    =IF(SUMPRODUCT(COUNTIF(M$5:S$5,B3:H3))>3,"WIN","LOSE")


    lot.jpg

  13. #13
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898
    What are your winning numbers list?

    I seem to get a "Lose" if on 3 shows up in my winning list....

    Can you post a zipped XL2003 file?

  14. #14
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675
    My initial assumption was that numbers wouldn't be repeated in either predictions or winning list. If there are 2 3s in the winning list this will give an incorrect count of 4.

    Do the last 2 in the list have to match against only the last 2 in the winning numbers?

  15. #15
    Registered User
    Join Date
    01-18-2008
    Posts
    24

    hmm

    Eh think this should be it close though.

    lot.zip

  16. #16
    Registered User
    Join Date
    01-18-2008
    Posts
    24

    oops

    Sorry daddylonglegs I replied before reading your question. Yes the last two numbers are lucky star numbers and can be the same as any of the first 5.

    PS how do I get rid of the annoying macro popup, I did have a pop up msg but removed it and reset the security ot medium.

    thanks

  17. #17
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898
    Quote Originally Posted by Davidcc
    Eh think this should be it close though.

    Attachment 17989
    It still seems to work for me... if 3 items are highlighted, I get a Win....

    what numbers are your winning numbers and which row(s) have incorrect outcome....

  18. #18
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675
    Can you explain exactly how this lottery works, I'm not familiar with the rules. Are you only matching the first 5 picks against the first 5 in the result and the 2 lucky stars against just the last 2?

  19. #19
    Registered User
    Join Date
    01-18-2008
    Posts
    24

    hmm

    try entering the following numbers and look at lines 3,9,25,62,63,66,70,83,86,90,97 etc

    enter 3 25 40 33 42 9 3

    Am I missing something very simple ?

  20. #20
    Registered User
    Join Date
    01-18-2008
    Posts
    24

    rules

    I think it would be easier for you to visit the site linked on the sheet for the rules rather than me try to ramble on in my probably confusing way way. But you can win by matching any three numbers not necessarily containg any from both sets.

  21. #21
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898
    Would this revised formula in J1, copied down, work?

    Please Login or Register  to view this content.

  22. #22
    Registered User
    Join Date
    01-18-2008
    Posts
    24

    hmm

    Tried it for all the rows still shows a win if any 2 numbers are repeated. Can a formula seperate the last two columns from the first five and then calculate the total matching numbers in a row ?

  23. #23
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898
    Which rows in the attached are not giving correct results...and why?
    Attached Files Attached Files

  24. #24
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675
    Yeah, I think you need to check the two sets of numbers separately so try this formula in J1 copied down

    =IF(SUMPRODUCT(COUNTIF(M$5:Q$5,B1:F1))+SUMPRODUCT(COUNTIF(R$5:S$5,G1:H1))>2,"WIN","LOSE")

    You also have to adjust the conditional formatting in a similar way. If you select columns B to F then the formula should be

    =MATCH(B1,$M$5:$Q$5,0)

    and then for columns G and H

    =MATCH(G1,$R$5:$S$5,0)

  25. #25
    Registered User
    Join Date
    01-18-2008
    Posts
    24

    Hmm

    Thanks guys.

    Dont know what I did I copied the formula you gave NVBC into the cells in column J but still got errors, so I,ve cheated, I saved your zipped file and it works perfectly. I'm going to play around with my old copy to see where I'm going wrong.

  26. #26
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675
    Well I looked up how Euromillions works and I don't think that will give the required results. As I said above, I think you need to check the 2 sets of numbers, the first 5 and the last 2, independently.

    As it stands, if you pick 3 and 9 as lucky stars and 2 and 7 in the main numbers then winning numbers has 2 and 7 as lucky stars and 3 and 9 as main numbers you'll be shown as a winner......but you don't have any matches

  27. #27
    Registered User
    Join Date
    01-18-2008
    Posts
    24

    Smile hmm

    test.jpg

    Hi guys,
    been working on this on and off (had to stop monitor was nearly out the window) not going to let it beat me er, well yous lol

    As you can see by the attachment I think I'm doing something very simple WRONG !
    The formula given by Daddylonglegs (I've only entered it in row 1 so ignore the rest) certainly seems to be correct ie a 6 or 2 anywhere in the 1st 5 cells does not show as a win but does in the last 2 "lucky star" cells, so bang on. It also shows as a win in J1. Only minor glitch is the winning numbers in the "lucky star" cells wont change to red.

    Am I being pernickity, it's just I hate being beaten.

    D

  28. #28
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898
    If you are still using the original Conditional Format fomrula that I gave you, it should highlight all the numbers.

    =MATCH(H1,results,0)

  29. #29
    Registered User
    Join Date
    01-18-2008
    Posts
    24

    hmm

    test2.zip

    Ok I'm going back to colouring in books......................

    NBVC I have attached the file. I have only been working on row 1 and the entry cells m5 - s5 please bear with me, a week ago I couldn't even save a file lol. If you can see the problem please can you enlighten me.

  30. #30
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675
    I think you need to apply conditional formatting as I suggested in my last post above, so if you select columns G and H then formula would be

    =MATCH(G1,$R$5:$S$5,0)

  31. #31
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675
    Sorry, didn't see your last post. You're using the correct formula but it seems to have picked up quotes around it (sometimes happens with CF). Try again without the quotes.

    Note: I think you still need to change the CF for column B to F also, to only check against M5:Q5

  32. #32
    Registered User
    Join Date
    01-18-2008
    Posts
    24

    hmm

    Daddy I did that, but upon checking Ive discovered what I think is the problem as its working now. When I copied and pated formula I included one of the "s

    thanks

    phew I need a cuppa.

  33. #33
    Registered User
    Join Date
    01-18-2008
    Posts
    24

    oops

    You beat me too it lol

  34. #34
    Registered User
    Join Date
    01-18-2008
    Posts
    24

    last I promise

    OK that seems to have sorted it, but do I really have to go down copying and inserting the CF for each set of A-F and G and H in every row, or is there a quick way.

  35. #35
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898
    Copy the first row and then select the remaining rows. Go to Edit|Paste Special and select Formats.

  36. #36
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675
    You don't really need to do any copying.

    If you select the entire columns G and H first (or just a limited range like G1:H200 with g1 active cell) then you can just apply the formula once, i.e.

    =MATCH(G1,$R$5:$S$5,0)

    This will adjust automatically for all the cells

  37. #37
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898
    Quote Originally Posted by daddylonglegs
    You don't really need to do any copying.

    If you select the entire columns G and H first (or just a limited range like G1:H200 with g1 active cell) then you can just apply the formula once, i.e.

    =MATCH(G1,$R$5:$S$5,0)

    This will adjust automatically for all the cells
    That's what I showed the OP from the beginning.... I thought because he did the formatting in the first row already, then he could just copy the formats to the others.... oh well.....whatever works.

  38. #38
    Registered User
    Join Date
    01-18-2008
    Posts
    24

    hmm

    help!!.zip

    Hi guys

    sorry,bet your getting bored with this thread. I don't get much time to look at this so bear with me please. I have I believe inserted the formula into all rows as you instructed but still some false returns are being shown and I cant figure out why. I've attached the current file if that helps. If you imput last fridays numbers 14 16 23 40 46 and 4 and 8 then on lines 105 120 143 146 you get a wrong result. Any ideas ?

  39. #39
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675
    I don't think you have the same formula allthe way down column J. Make sure you copy J1 all the way down. [You can select J1, put cursor on the bottom right corner of the cell until you see the black + and then double-click]

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1