Need a button to search for out of tolerance values

I'm uploading a sample excel sheet to illustrate what I'm needing so any help would be appreciated. My actual sheet will host a lot of data.

worksheet 1 will be where people enter test data

worksheet 2 will be where the specs are put that tells the tolerances of the products tested

I would like a button in worksheet one that when mashed will cause the out of tolerance values on worksheet 1 to turn bold red.

I am a beginner and can do very simple macro tasks.


Thank you very much for the help and the education.

Thank you for looking into it.

If the values from sheet 1 fall outside the min/max on sheet 2 for that particular code header than it is considered out of tolerance.

Works wonderfully thank you so much.

Hi sintek

Nice solution to the OP's problem. I have used auto filter but my solution could be improved as I'm not to happy with the copy and paste part.

You got any idea about that?

Alf

Hello Sintek,

I hate to trouble you after your assistance but I am having trouble applying your solution to the final draft of my sheet. In all honesty it is my lack of VBA understanding preventing me from being able to make proper adjustments and substitutions. If I uploaded my final draft would you do the same code for it? It is along the same concept.

If not, no problem. I do not wish to trouble you especially after the assistance you have already provided me.

Thank you!

I just entered the values that would need to be checked in the tolerances tab. Some do not have an upper tolerance but they are clearly marked. This will not only help me with this sheet but help me a better understanding.

Thank you

The data entry will be ongoing in sheet 1 titled "Data Entry". The tolerances are entered on sheet 2 labeled "Tolerances". I just entered the values you needing to be cross checked. For example, when I mash the button with the macro I would like for it to check what is out of limits. So on the data entry sheet where it says 45678 has a entry of 22.24 under "Max Load daN" that it checks that value on the tolerance sheet to see that 45678 has a min. of 13 so that one is ok. For tolerances have a min and max it should fall in-between the two. Any values from sheet "Data Entry" that are out of tolerance should turn bold red. If the tolerance does not have the category from sheet "Data Entry" then it does not need to be checked.


If I caused any confusion or I'm not clear than please let me know.

Thank you Leith Ross. Tomorrow I will try to plug in your conditional format suggestion into the Final Draft version I uploaded here and see how it works. I am interested in your formatting and Sintek's macro attempt to help me get a better understanding. Will your formatting work with the Final Draft attachment I uploaded today? I can do small tasks but stringing longer ones together is where I get lost.

Sintek,

I re-uploaded the Final Draft sheet and renamed it "Final Draft - Color Coded". I highlighted the cells on the "Data Entry" tab a particular color. I then highlighted the cells on the tab "Tolerances" the same color to show where the values in "Data Entry" are checked against to show if they are within tolerance. Some only have a min and not a max. The cells not highlighted a particular color will not need to be checked against another number. I added a third tab titled "Answer Key" which shows exactly what cells should be out once the button is pushed. Thank you again for your time and Leith I am fixing to try your format.

Sorry Sintek.

The tolerance of the spec is set up in daN but the machine tests in lbf so they will enter the force in lbf and it converts into daN. The same is about true for the square meters. Tolerance shows in Y2, sheet converts it to M2 and in the future we look to switch to that.

Also if it will make it easier for the ones missing a max limit we can add one into the tolerance sheet and just set it at a ridiculous limit.



Leith,

I'm sure it is me but getting your format to transfer over has proven difficult. I am still trying.

I figured it might be easier, just insert a column beside those with just a min and make a max column. Use whatever value you like and I'll raise it to the extreme on sheet once your excel Jedi skills solve it. Plus I think it becomes much more like the original sheet I posted. I'm sorry for the trouble but it does help me tremendously.

AMAZING! it is 99% there. It works perfectly except columns (as you have the numbered in "Data Entry") 22 and 23 show all red. I'm sure it is my fault not mentioning it but they do two sets of testing for that section so column 19 and 22 use the exact tolerances and 20 and 23 fall under the same tolerances.

I can not tell you how awesome this is. Great job. Is there something I can add to the code to include 22 and 23 being checked under the same as 19 and 20?

Thank you again man

I really can't thank you enough. Besides helping on this sheet you helped me for years to come when I try to understand it and hopefully apply it to future sheets.