Find cells in a large array

[zw1ck]

I need to search through two 1000 by 16 arrays and highlight points that are outside of a tolorence of values determined by the user. I have very little idea of where to start. I never fully grasped how to make arrays. After I get how to make the array I think I can figure it out from there. Many thanks if anyone can help.

 

[venkat1926]

give a very small extract of data 10 rows by 5 columns and explain what you want to find in this data.

 

[zw1ck]

These values are supossed to be exactly 12. I need to make a code that when a person enters how far from 12 is acceptable it will highlight all of the points that are outside of the range.

12.77474	12.76072	12.70904	12.41403	12.79048
12.99941	12.0157	12.10005	12.79888	12.04565
12.62764	12.09797	12.69449	12.83482	12.54336
12.784	12.75369	12.83273	12.21037	12.10545
12.09793	12.27295	12.75069	12.67365	12.0899
12.21941	12.13042	12.34539	12.92295	12.40642
12.58453	12.87597	12.19063	12.74742	12.78214
12.43114	12.2753	12.80259	12.4176	12.27717
12.06449	12.70051	12.15544	12.32614	12.05026
12.37281	12.77083	12.44699	12.87838	12.37443

<colgroup sab="775"><col style="width: 48pt;" span="5" width="64" sab="776"> <tbody sab="777">
</tbody>

 

[Peter_SSs]

Here's one way. For my layout, select from A2 to E11 and apply the Conditional Formatting (CF) shown.
If you need help with details of how to apply the CF, post back which Excel version you are using.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
1										Target	Tolerance
2	12.7747	12.7607	12.709	12.414	12.7905					12	0.7
3	12.9994	12.0157	12.1001	12.7989	12.0457
4	12.6276	12.098	12.6945	12.8348	12.5434
5	12.784	12.7537	12.8327	12.2104	12.1055
6	12.0979	12.273	12.7507	12.6737	12.0899
7	12.2194	12.1304	12.3454	12.923	12.4064
8	12.5845	12.876	12.1906	12.7474	12.7821
9	12.4311	12.2753	12.8026	12.4176	12.2772
10	12.0645	12.7005	12.1554	12.3261	12.0503
11	12.3728	12.7708	12.447	12.8784	12.3744
12
Outside Tolerance

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2	1. / Formula is =ABS(A2-$J$2)>$K$2	Abc

 

[zw1ck]

I wish it could be that simple but I need to use VBA. I think I found a code that could work. I'll post it if it works.

 

[Peter_SSs]

I wish it could be that simple but I need to use VBA.

Perhaps you could tell us a bit more about this if the following does not solve your problem.
For example, could the Conditional Formatting simply be applied by the vba?
If vba is being used, where do we get the target value from? Or is it always 12?
How will the user be providing the tolerance value to the vba? Will it be in a cell? (which cell?) Will the code provide an InputBox? etc

For this suggestion, I have assumed that a table can be determined by starting at cell A1 and expanding until a complete column and row break is encountered.
For now at least I've hard-coded the target value and the tolerance into the macro.

Anyway, give it a try in a copy of your workbook.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Tolerance()<br>  <SPAN style="color:#00007F">Dim</SPAN> a<br>  <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, j <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, rws <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, cols <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>  <br>  <SPAN style="color:#00007F">Const</SPAN> Tgt <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Double</SPAN> = 12<br>  <SPAN style="color:#00007F">Const</SPAN> Tol <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Double</SPAN> = 0.7<br>  <br>  Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>  <SPAN style="color:#00007F">With</SPAN> Range("A1").CurrentRegion<br>    .Cells.Interior.ColorIndex = xlNone<br>    a = .Value<br>    rws = <SPAN style="color:#00007F">UBound</SPAN>(a, 1)<br>    cols = <SPAN style="color:#00007F">UBound</SPAN>(a, 2)<br>    <SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> rws<br>      <SPAN style="color:#00007F">For</SPAN> j = 1 <SPAN style="color:#00007F">To</SPAN> cols<br>        <SPAN style="color:#00007F">If</SPAN> IsNumeric(a(i, j)) <SPAN style="color:#00007F">Then</SPAN><br>          <SPAN style="color:#00007F">If</SPAN> Abs(Tgt - a(i, j)) > Tol <SPAN style="color:#00007F">Then</SPAN> .Cells(i, j).Interior.ColorIndex = 4<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>      <SPAN style="color:#00007F">Next</SPAN> j<br>    <SPAN style="color:#00007F">Next</SPAN> i<br>  <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>  Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>