1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Conditional Formatting in a Range

Discussion in 'Ask an Excel Question' started by andrefraga27, Aug 16, 2017.

  1. andrefraga27

    andrefraga27 New Member

    Messages:
    8
    I have numbers in a range from B5 to P35 which I need to color them everytime they match to the ones in B2 to P2. Each number in the range of B5 to P35 need be colored if it matches one number in the line B2 to P2.

    Help, please.
  2. p45cal

    p45cal Well-Known Member

    Messages:
    884
    Select cells B5:P35, ensuring that B5 is the active cell. Then in conditional formatting, use a formula to determine which cells to format and enter this formula:
    =ISNUMBER(MATCH(B5,$B$2:$P$2,0))
    choose your format and you're done.
    Last edited: Aug 16, 2017
  3. andrefraga27

    andrefraga27 New Member

    Messages:
    8
    Thank you, that worked fine!
    Another doubt, I also need to find the 1st, 2nd, 3rd...and 15th more occorring number in the range.

    Could you help please?

    Thanks a lot!
  4. p45cal

    p45cal Well-Known Member

    Messages:
    884
    Is this completely independent of the numbers in row 2?
    If so, supply a file so that we know what sort of numbers we're dealing with.
    Last edited: Aug 17, 2017
    andrefraga27 likes this.
  5. andrefraga27

    andrefraga27 New Member

    Messages:
    8
    Attached is the file.
    Each individual line can't repeat one number already in the same line, but other line can repeat the number in another line and even repeat the entire line. What I need now is to check in the entire range (now it is from B5 to P34) what are the most commons numbers (sometimes more than one number will be the most common) and how many times they repeat.
    Sorry for my english.

    Attached Files:

  6. p45cal

    p45cal Well-Known Member

    Messages:
    884
    See attached

    Attached Files:

  7. andrefraga27

    andrefraga27 New Member

    Messages:
    8
    That is perfect! Thanks a lot! Thanks, above all, for your patience!
  8. deciog

    deciog Member

    Messages:
    89
    Andrefraga27

    It confers cells Q5 through Q34, I changed the formula so it does not need an auxiliary worksheet.

    I hope I have helped

    Decio

    Attached Files:

  9. andrefraga27

    andrefraga27 New Member

    Messages:
    8
    Thank you Decio! Much better your way!
    You both helped a lot, but... every time I work this spreadsheet, something new happens.
    Now I'm having a problem with the data validation. Since I cant repeat a number in the same line I created a Data Validation that doesn't allow this and immediately prompt if the user tries this. But, as you can see, if I already have a number in the line just above and try insert the same number in the line bellow, the prompt will show anyway.
    May you help?
  10. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,817
    Hi ,

    Shouldn't the Data Validation formula be :

    =COUNTIF($B5:$P5,B5)=1

    so that the count in any one row is not more than 1 ?

    Narayan
    Thomas Kuriakose likes this.
  11. andrefraga27

    andrefraga27 New Member

    Messages:
    8
    Well, it's working the way the formula is written.
  12. p45cal

    p45cal Well-Known Member

    Messages:
    884
    I agree with Narayan; Have you tried changing cell O5 of your file from 22 to 21?
  13. andrefraga27

    andrefraga27 New Member

    Messages:
    8
    I did as you and p45cal suggested and still did not work.
    I'm going crazy!
  14. p45cal

    p45cal Well-Known Member

    Messages:
    884
    Select all the range B5:P34 (B5 should be the active cell), go into Data Validation and enter Narayan's formula.
  15. deciog

    deciog Member

    Messages:
    89
    Andrefraga27

    Follow the spreadsheet with the formula in the validation

    I hope I have helped

    Decio

    Attached Files:

  16. andrefraga27

    andrefraga27 New Member

    Messages:
    8
    Worked!! Thank you !!

Share This Page