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. ### andrefraga27New 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.

2. ### p45calWell-Known Member

Messages:
1,215
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. ### andrefraga27New 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.

Thanks a lot!
4. ### p45calWell-Known Member

Messages:
1,215
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. ### andrefraga27New 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.

File size:
17.7 KB
Views:
7

Messages:
1,215
See attached

File size:
18.9 KB
Views:
6
7. ### andrefraga27New Member

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

Messages:
112
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

File size:
14.6 KB
Views:
8
9. ### andrefraga27New 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. ### NARAYANK991Excel Ninja

Messages:
16,619
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. ### andrefraga27New Member

Messages:
8
Well, it's working the way the formula is written.
12. ### p45calWell-Known Member

Messages:
1,215
I agree with Narayan; Have you tried changing cell O5 of your file from 22 to 21?
13. ### andrefraga27New Member

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

Messages:
1,215
Select all the range B5:P34 (B5 should be the active cell), go into Data Validation and enter Narayan's formula.
15. ### deciogActive Member

Messages:
112
Andrefraga27

I hope I have helped

Decio

File size:
14.6 KB
Views:
3
16. ### andrefraga27New Member

Messages:
8
Worked!! Thank you !!