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

##### New Member
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

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
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.

Replies
4
Views
181
Replies
3
Views
432
Replies
3
Views
279
Replies
7
Views
232
Replies
0
Views
147

1,219,672
Messages
6,149,617
Members
450,904
Latest member
Gracifer

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

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