# 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.

Thanks
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ConsultantCommented:

Kevin
EngineerCommented:
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.
ConsultantCommented:
[B1:D1].Find(Application.Max([B1:D1])).Offset(1).Value = 50

Kevin

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Network ManagerCommented:

That will return the cell in row 1 that contains the highest value.
EngineerCommented:
Or maybe

[B1:D1].Find(Application.Max([B1:D1])).offset(1,0).Value = 50
Network ManagerCommented:
Here is an example of what I posted:
excel-highest.jpg
Author 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!
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.