Code to trap data entry outside of range

[Alan Campbell]

Hi,

I found this code from Bob Phillips that evaluates a cell entry
against the previous cells entry in the column and generates a
warning. Can anybody help me modify this code to evaluate an average
of the previous 10 rows (i.e. a range)?

Thanks.

Alan

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ans
Application.EnableEvents = False
On Error GoTo sub_exit
With Target
If (.Column > 2 And .Column < 9) Then
If .Row = 3 Or .Row = 8 Or .Row = 13 Or .Row = 14 Then
If .Value > .Offset(0, -1) * 1.2 Then ' 20% greater
ans = MsgBox("Value of " & .Value & " is more than
20%
greater " & _
"than the " & .Offset(0, -1).Value &
" for
" & Format(.Offset(-.Row + 1, -1), "dd mmm yyyy") & vbCrLf & _
vbCrLf & _
"Are you sure? Please recheck entry!"
&
vbCrLf & _
"(hit 'OK' to re-input, 'Cancel' to
accept
and ignore limit)", _
vbOKCancel, _
"Vinay's Value Checker")
If ans = 2 Then
.Offset(1, 0).Activate
End If
End If
End If
End If
End With
sub_exit:
Application.EnableEvents = True
End Sub

 

[Bob Kilmer]

use something like this

If .Value > WorksheetFunction.Average(Range(.Offset(0, -1),
..Offset(0, -10))) Then

You will have to assure that Offset(0, -1) and .Offset(0, -10) are not off
the sheet, possibly by ignoring Targets too close to the top of the sheet
using, perhaps using something like,

If Intersect(Target, Range(.Rows(1),.Rows(10))) Then Exit Sub

 

[Bob Kilmer]

Rows, not Columns: Range(.Offset(-1, 0), .Offset(-10, 0))

 

[Alan Campbell]

Bob,

Thanks. I will give try your coding changes and let you know how is goes.

Alan