creat a pop up box

[delete automatically]

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]

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]

I do not need these the same time

I just need the one I have for minimum and now I need one for a differnent
row for maximum

The maximum will be in column D

thanks

 

[delete automatically]

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]

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.

 

[Zack Barresse]

Hi,

Maybe something like this ....


Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim varVal As Variant
If Target.Cells.Count > 1 Then Exit Sub
If IsNumeric(Target.Value) = False Then Exit Sub
Call ToggleEvents(False)
Select Case Target.Column
Case 1 To 6
varVal = Target.Value
With Application.WorksheetFunction
If varVal < .Max(varVal, Me.Columns(Target.Column)) Then
MsgBox "Not Max - column " & Choose(Target.Column, "A", "B",
"C", "D", "E")
Else
MsgBox "Max - column " & Choose(Target.Column, "A", "B",
"C", "D", "E")
End If
End With
End Select
Call ToggleEvents(True)
End Sub

Public Sub ToggleEvents(blnState As Boolean)
'// Written by Zack Barresse, aka firefytr
With Application
.DisplayAlerts = blnState
.EnableEvents = blnState
.ScreenUpdating = blnState
If blnState = True Then
.CutCopyMode = False
.StatusBar = False
End If
End With
End Sub


HTH

 

[delete automatically]

Dave,

Would there be an easier way to do this, like using data validation?
That way I could do each column seperatley and have differnent saying for
each columns pop up box?

 

[Dave Peterson]

Maybe...

Try this on a test worksheet
Select column E
With E1 the Activecell
Data|Validation
On the Settings tab:
Allow: Custom
formula: =OR(ISNUMBER(E1)=FALSE,COUNTIF(E:E,">="&E1)>1)

On the Error Alert tab:
Style: Information
Message: New Record

Dave,

Would there be an easier way to do this, like using data validation?
That way I could do each column seperatley and have differnent saying for
each columns pop up box?

 

[delete automatically]

I think were almost there.
In column e rows 3 thru 33 when they enter a number in a cell and its the
max number in that row then have it say NEW RECORD.

Thanks again

 

[Dave Peterson]

Untested...
Select E3:E33
With E3 the activecell

=OR(ISNUMBER(E3)=FALSE,COUNTIF(E$3:E$33,">="&E3)>1)

I think were almost there.
In column e rows 3 thru 33 when they enter a number in a cell and its the
max number in that row then have it say NEW RECORD.

Thanks again

 

[delete automatically]

Dave,

That worked perfectly,
Thanks a million

Untested...
Select E3:E33
With E3 the activecell

=OR(ISNUMBER(E3)=FALSE,COUNTIF(E$3:E$33,">="&E3)>1)