• 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...

  • 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

andrefraga27

New Member
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.
 
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:
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!
 
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.
 

Attachments

  • pai_english.xlsx
    17.7 KB · Views: 7
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?
 
Select all the range B5:P34 (B5 should be the active cell), go into Data Validation and enter Narayan's formula.
 
Andrefraga27

Follow the spreadsheet with the formula in the validation

I hope I have helped

Decio
 

Attachments

  • Chandoo35479pai_english Decio 2.xlsx
    14.6 KB · Views: 3
Back
Top