Hello all,
I would like to have a macro to highlight outliers for each column. The outliers are the values which is not missing , but are out of limits.

The quartile ranges IQR is(use column D as example):
IQR=Q3-Q1=QUARTILE(D2:D44,3)-QUARTILE(D2:D44,1)
upper limit is Q3+IQR
lower limit is Q1+IQR



Here is the macro I recorded for using one test data(one column)

'Sub findoutlier()
''
'' findoutlier Macro
''
''
Range("A1:A43").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND($A1 < ""."", OR($A1<(QUARTILE(A1:A43, 1)-1.5*(QUARTILE(A1:A43, 3)-QUARTILE(A1:A43, 1))), $A1(QUARTILE(A1:A43, 1)+1.5*(QUARTILE(A1:A43, 3)-QUARTILE(A1:A43, 1)))))"
Selection.FormatConditions(Selection.FormatConditi ons.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
End Sub


I want to loop over 30 columns, and I added loops, but it does not work :(

Sub findoutlier()
'
' findoutlier Macro
'
'
Dim i As Long
Dim j As Long
Sheets("Sheet1").Select
For i = 1 To 30
For j = 1 To 44
' Range(Cells(2, i + 3), Cells(44, i + 3)).Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND(cells(j, i+3) < ""."", OR (cells(j, i+3)<(QUARTILE(range(cells(2, i+3), cells(44, i+3)), 1)-1.5*(QUARTILE(range(cells(2, i+3), cells(44, i+3)), 3)-QUARTILE(range(cells(2, i+3), cells(44, i+3)), 1))), (cells(j, i+3)(QUARTILE(range(cells(2, i+3), cells(44, i+3)), 1)+1.5*(QUARTILE(range(cells(2, i+3), cells(44, i+3)), 3)-QUARTILE(range(cells(2, i+3), cells(44, i+3)), 1)))))"
Selection.FormatConditions(Selection.FormatConditi ons.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False

Next j
Next i

End Sub

I am not sure if the error is cause by "(QUARTILE(range(cells(". Some similar test takes a long time to loop through all cells and I also would like suggestion on how to make this more efficient.

Any help would be much appreciated.

Haley