It seems different from what your original post wanted.

Did you try to modify that earlier suggestion? Take a look at that, give it a
try and post back if you have questions.

delete automatically wrote:

Actually what I need is, I have 6 columns (categorys) a thru f and in each
category I would like to see NEW RECORD when they enter the maximum amount.

So when they enter like 80 and thats the max in column a then say NEW RECORD
and if they enter 50 in column b, and thats the max in that coumn then say
NEW RECORD.

Does that make sense to you?
Thanks

"Dave Peterson" wrote:

Do you need both of these at the same time?

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim x As Variant

'one cell at a time
If Target.Cells.Count 1 Then
Exit Sub
End If

If Not (Intersect(Target, Me.Range("c:c")) Is Nothing) Then
'do column C
Application.EnableEvents = False
x = Target.Value
Target.Value = ""
If x < Application.WorksheetFunction.Min(Me.Range("c:c")) Then
MsgBox "NEW RECORD"
End If
Target.Value = x
Application.EnableEvents = True

ElseIf Not (Intersect(Target, Me.Range("d:d")) Is Nothing) Then
'do column D
Application.EnableEvents = False
x = Target.Value
Target.Value = ""
If x Application.WorksheetFunction.Max(Me.Range("d:d")) Then
MsgBox "NEW RECORD"
End If
Target.Value = x
Application.EnableEvents = True

End If

End Sub


You may want to remove the clearing, checking, and replacing with something
like:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

'one cell at a time
If Target.Cells.Count 1 Then
Exit Sub
End If

If IsNumeric(Target.Value) = False Then
Exit Sub
End If

If IsEmpty(Target.Value) Then
Exit Sub
End If

If Not (Intersect(Target, Me.Range("c:c")) Is Nothing) Then
'do column C
If Application.CountIf(Me.Range("C:C"), "<" & Target.Value) = 0 Then
MsgBox "NEW RECORD"
End If

ElseIf Not (Intersect(Target, Me.Range("d:d")) Is Nothing) Then
'do column D
If Application.CountIf(Me.Range("d:d"), "" & Target.Value) = 0 Then
MsgBox "NEW RECORD"
End If

End If

End Sub

But I'm not sure what you really want to do with ties.

delete automatically wrote:

I have a formula that when a cell in column C is the minimum it will say NEW
RECORD.
What I need now is if a cell in column D is the maximum the say NEW RECORD
Here is the formula I have for the minimun in column c

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("c:c")) Is Nothing Then Exit Sub
Application.EnableEvents = False
x = Target: Target = ""
If x < Application.WorksheetFunction.Min(Range("c:c")) _
Then MsgBox ("NEW RECORD")
Target = x
Application.EnableEvents = True
End Sub

I took min out and put max and changed the ("c:c")) to ("d:d"))
but does not work
anything else I need to do?

Thanks

--

Dave Peterson


--

Dave Peterson