msg box

Here's one possible way to do this

Cliff,

There are a few ways to attack this, so let's take a simple and effective route. In answer to your first requirement, the formula for finding the highest numeric value in range C2:C23 would be:
=MAX(C2:C23), which you could put in C24 or wherever you want.
The formula to find the corresponding person's name in A2:A23 which you could put in cell B24 could be:
=INDEX(A2:A23,MATCH(C24,C2:C23,0))
Note, I don't recommend a VLOOKUP formula because it is likely that your numbers in C2:C23 will not be in ascending or descending order, so they might not return an accurate person's name.

Then, to have the message box be the medium by which you'd like the maximum value and person's name to be displayed, how about a worksheet change event for the range C2:C23. Right-click on your sheet tab name, then left click on View Code, and paste this in:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Target, Range("C2:C23")) Is Nothing Then Exit Sub
MsgBox "The maximum number is " & Range("C24").Value _
& vbCrLf & "for the person named " & Range("B24").Value & "."
End Sub

You can always hide row 24 where the formulas are, so as not to steal the message box's thunder, and then whenever you make a change to C2:C23, a message box will pop up to tell you the latest scoop.

As usual, there are alternative solutions that you or others may decide upon; this is just one suggestion.

Hope this gets you started.

Tom Urtis

That worked great but?

Tom,

The formula worked great, thanks. But I guess I should have told you how the figures were figured because it only worked when I entered figures in the range of cells in the formula. The range of cells get their contents from adding two other cells together. Then I need for the msg to appear. I have one column for one weeks numbers then another column for the second weeks numbers and then the range figures these two columns and then needs to produce the message box. It worked as long as the figures were entered into the range but not when I entered them into the other two columns. Also, is there any way for the msg. to appear only after all figures have been entered? Right now it pops up during each entry.

thanks

Re: That worked great but?

Cliff,

Thanks for clarifying your situation. Only my opinion, but one suggestion is to delete the worksheet change event code I sent you, and place this code in a VBA module:

Sub MessageAlert()
MsgBox "The maximum number is " & Range("C24").Value _
& vbCrLf & "for the person named " & Range("B24").Value & "."
End Sub

I was unaware that you were changing all 22 records at a time, so it's easy to see how that pop up box would be a burden in that case.

What you could do instead is to attach the above macro to a command button that you could draw onto your worksheet. Label the button with something like "Click here to see who has the highest number."

This way, the user has control over when to see the results, and it'd only take one mouse click.

Tom Urtis

Excellent work. Who needs the book when you learn right here.

Thanks for clarifying your situation. Only my opinion, but one suggestion is to delete the worksheet change event code I sent you, and place this code in a VBA module: Sub MessageAlert()

: Tom, : The formula worked great, thanks. But I guess I should have told you how the figures were figured because it only worked when I entered figures in the range of cells in the formula. The range of cells get their contents from adding two other cells together. Then I need for the msg to appear. I have one column for one weeks numbers then another column for the second weeks numbers and then the range figures these two columns and then needs to produce the message box. It worked as long as the figures were entered into the range but not when I entered them into the other two columns. Also, is there any way for the msg. to appear only after all figures have been entered? Right now it pops up during each entry. : thanks