Find cells in a large array

zw1ck

New Member
Joined
Feb 28, 2013
Messages
19
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.
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

venkat1926

Well-known Member
Joined
Aug 21, 2005
Messages
4,824
give a very small extract of data 10 rows by 5 columns and explain what you want to find in this data.
 

zw1ck

New Member
Joined
Feb 28, 2013
Messages
19
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.7607212.7090412.4140312.79048
12.9994112.015712.1000512.7988812.04565
12.6276412.0979712.6944912.8348212.54336
12.78412.7536912.8327312.2103712.10545
12.0979312.2729512.7506912.6736512.0899
12.2194112.1304212.3453912.9229512.40642
12.5845312.8759712.1906312.7474212.78214
12.4311412.275312.8025912.417612.27717
12.0644912.7005112.1554412.3261412.05026
12.3728112.7708312.4469912.8783812.37443

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

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
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
 

zw1ck

New Member
Joined
Feb 28, 2013
Messages
19
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
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>
 

Watch MrExcel Video

Forum statistics

Threads
1,123,125
Messages
5,599,841
Members
414,342
Latest member
K Darrell Smith

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top