asked on

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

zorvek (Kevin Jones)

MsgBox [B1:D1].Find(Application.Max([B1:D1])).Address

Kevin

Saqib Husain

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.

ASKER CERTIFIED SOLUTION

zorvek (Kevin Jones)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Steven Carnahan

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.

Saqib Husain

Or maybe

[B1:D1].Find(Application.Max([B1:D1])).offset(1,0).Value = 50

Steven Carnahan

Here is an example of what I posted:
excel-highest.jpg

mike637

ASKER

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!