Highlighting a row based on the value of a cell when compared to another cell

Ondulado

New Member
Joined
Aug 23, 2014
Messages
7
HI

I am sure one of you excel gurus out there can help me with this little problem.

I want to be able to highlight rows on one excel sheet when the value of a cell is the highest in a range, but based on the value of another cell.

Let me explain a bit more.... :)

If cell F3 on sheet 1 "Real" is 60

The formula will look at sheet 2 "Costs" - Column C7-C44 to find the matching number 60
which in this case would be Row 31

Then it will look across at Column I7-I31 and find the highest Value and highlight the Row containing the highest value in Blue

It will also look at Column J7-J31 and find the highest Value and highlight the Row containing the highest value in Green

This would work no matter the number on Sheet one, which could be up to 300

The formula would look at all rows before the Row that contains the matching number and find the highest value in both columns and highlight that row.

So if the number on sheet one is 100 and appears in corresponding Row 39 on sheet 2. Then the formula would search I7-I39 and J7-J39 to find the highest and highlight the rows containing the highest numbers in the appropriate colour.

I want to post a screen shot here but I can't post attachments for some reason
I hope this is clear..

Thanks
Jules
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

Ondulado

New Member
Joined
Aug 23, 2014
Messages
7
ooops I forgot to mention. If the number on the first sheet is for example 64 and on the 2nd sheet there is 60 and 65. The formula will look at the nearest low number. ie 60 to find the below this.
 
Upvote 0

Forum statistics

Threads
1,191,171
Messages
5,985,067
Members
439,938
Latest member
MAlhash

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top