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

[Ondulado]

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

 

[Ondulado]

Screenshot on Google Drive. - https://drive.google.com/file/d/0BxJSUVCiDg0fN3l2SExxekdzYnM/edit?usp=sharing

 

[Ondulado]

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.