How to find a data anomaly in a row

Ed Harris

Board Regular
Joined
Dec 9, 2017
Messages
58
Office Version
  1. 2010
Platform
  1. Windows
Hello,
I am trying to highlight anomalous values in my data by colouring the cell. I was kindly given a macro by offthelip last year which was really usefull but now I have much more data and the problem is that the macro highlights the cells either side of the anomaly as well. I havent been able to improve the macro myself. Is there a better mathematical expression for "mecells" that will select just the anomalous value to highlight? here is the macro and data example.

Sub testyellow()
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
inarr = Range(Cells(1, 1), Cells(lastrow, 30))
For I = 3 To lastrow
For j = 2 To 25
mecells = (inarr(I, j - 1) + inarr(I, j + 1)) / 2
Delta = Abs(mecells - inarr(I, j))
If Delta >= 0.004 Then
With Range(Cells(I, j), Cells(I, j)).Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = RGB(255, 255, 0) 'yellow
.TintAndShade = 0
.PatternTintAndShade = 0
End With

End If
Next j
Next I
End Sub

Beam2 2022.xlsm
ABCDEFGHIJKLMNOPQRSTUVWXY
173.415773.4155773.4157673.4154473.4157973.4152573.4148273.4153873.4153173.4153973.4152573.4156373.4150273.4154773.4149273.4154173.4156373.4158473.415273.4156273.4153173.4153773.4151173.4149473.41538
273.4131573.4133573.4132473.4131973.4132573.4131273.4130673.4130373.4131873.4130373.4131573.4130273.4131473.4131773.4131173.4131973.4131873.4131173.4131173.4131673.4132573.4130773.4131373.4132873.41306
373.4120973.4225573.4119273.4120673.4113573.4122973.4752673.4103273.4122573.4121373.3695573.4121673.4120873.3507173.412373.41273.4124773.4118573.41273.4120773.4124873.4122273.4120673.3683173.41209
473.4158673.415573.4153173.4153573.4151673.4153473.4161273.4152573.4152173.4158973.4154773.4152673.4153473.4149573.4154473.4154473.4160273.4151873.4145973.4145773.4145873.4153473.414873.415473.41493
573.4154473.4153473.415473.4151873.4152273.4156673.415573.4158673.4154473.4152873.4150873.4155673.4155173.415673.4153973.4148873.415673.4152573.4153173.4150873.4153173.4152373.4157373.4156773.41564
6
7
8-0.010450.010627-0.000150.000718-0.00095-0.062970.062966-0.064940.001938-0.00012-0.042580.04261-8.4E-05-0.061370.061592-0.00030.000473-0.000620.0001457.6E-050.000412-0.00027-0.00015-0.043760.043777
Sheet5
Cell Formulas
RangeFormula
A8:F8A8=A3-B3
G8:Y8G8=G3-F3
 
Sorry I don't understand, did you mean columns. Its columns 1440 to 2880 that are of interest.
 
Upvote 0
Yes, sorry. The code correctly uses columns but I used the wrong word when I said "rows". I did mean "columns."
 
Upvote 0

Forum statistics

Threads
1,226,837
Messages
6,193,257
Members
453,785
Latest member
SClark702025

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
Back
Top