# Find cells in a large array

#### zw1ck

##### New Member
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.

### Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
give a very small extract of data 10 rows by 5 columns and explain what you want to find in this data.

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.7747 12.7607 12.709 12.414 12.7905 12.9994 12.0157 12.1 12.7989 12.0457 12.6276 12.098 12.6945 12.8348 12.5434 12.784 12.7537 12.8327 12.2104 12.1054 12.0979 12.2729 12.7507 12.6737 12.0899 12.2194 12.1304 12.3454 12.923 12.4064 12.5845 12.876 12.1906 12.7474 12.7821 12.4311 12.2753 12.8026 12.4176 12.2772 12.0645 12.7005 12.1554 12.3261 12.0503 12.3728 12.7708 12.447 12.8784 12.3744

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

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
ABCDEFGHIJK
1TargetTolerance
212.774712.760712.70912.41412.7905120.7
312.999412.015712.100112.798912.0457
412.627612.09812.694512.834812.5434
512.78412.753712.832712.210412.1055
612.097912.27312.750712.673712.0899
712.219412.130412.345412.92312.4064
812.584512.87612.190612.747412.7821
912.431112.275312.802612.417612.2772
1012.064512.700512.155412.326112.0503
1112.372812.770812.44712.878412.3744
12
Outside Tolerance
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A21. / Formula is =ABS(A2-\$J\$2)>\$K\$2Abc

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.

I wish it could be that simple but I need to use VBA.
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>

Replies
2
Views
153
Replies
10
Views
693
Replies
5
Views
130
Replies
5
Views
191
Replies
2
Views
196

1,196,408
Messages
6,015,102
Members
441,870
Latest member
kojack

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back