Determine cell with greatest value

Hello Experts,

I want my code on any worksheet_change (usedRange A1 to H20) that it determines in the Range(B1:D1) which cell has the highest numeric value.

Then
If B1 is highest then B2.value = 50
elseif C1 is highest then C2.value = 50
elseif D1 is highest then D2.value = 50
end if

This is the basis for what I need it to do - I have other steps to add after it determines the right cell.  But this will get me started and probably finished.

Can you assist please,
Thanks
mike637Asked:
Who is Participating?
 
zorvek (Kevin Jones)Connect With a Mentor ConsultantCommented:
[B1:D1].Find(Application.Max([B1:D1])).Offset(1).Value = 50

Kevin
0
 
zorvek (Kevin Jones)ConsultantCommented:
MsgBox [B1:D1].Find(Application.Max([B1:D1])).Address

Kevin
0
 
Saqib Husain, SyedEngineerCommented:
Why don't you simply enter this formula in B2 and then copy it all the way to D2?

=IF(B1=MAX($B$1:$D$1),50,"")

This way you would not need any event macro.
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
pony10usCommented:
Will this work:   =CELL("address",INDEX(1:1,MATCH(MAX(1:1),1:1,0)))

That will return the cell in row 1 that contains the highest value.
0
 
Saqib Husain, SyedEngineerCommented:
Or maybe

[B1:D1].Find(Application.Max([B1:D1])).offset(1,0).Value = 50
0
 
pony10usCommented:
Here is an example of what I posted:
excel-highest.jpg
0
 
mike637Author Commented:
Hello Experts,

I think I am a bit brain-fried, but I was going to use it as a worksheet.change, but it needs to be on worksheet.activate.

I think I need something similair to what was proprosed in the

[B1:D1].Find(Application.Max([B1:D1])).Offset(1).Value = 50

But how do I declare this to work in a worksheet.activate situation?

Thanks Experts!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.