Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 186
  • Last Modified:

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
0
mike637
Asked:
mike637
  • 2
  • 2
  • 2
  • +1
1 Solution
 
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
 
zorvek (Kevin Jones)ConsultantCommented:
[B1:D1].Find(Application.Max([B1:D1])).Offset(1).Value = 50

Kevin
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now