Highlight a cell in a range depending on the maximum value in another range

[7ank0v1c]

Heya,

So I am having an issue which I can't seem to solve.

I am fairly new to excel formulas so please forgive me if this is a dumb question.

So here it is:

I am trying to build a conditional formatting formula which would highlight a cell in range B20 to AF20, based of the highest number in row range B30 to AF30. The cell I want to be highlighted in Row B20 to AF20 is the one which is in the same column as the highest figure in row range B30 to AF30.

I also would like the exact same but with the smallest figure.

I have currently got this: =B30:AF30<=SMALL(B30:AF30,1)
but it isnt working how I want it to

Any ideas?

Thanks in advance

[Special-K]

Select B20:AF20

Conditional Formatting
New Rule
Use a formula to determine...

=(B30:AF30=MAX($B30:$AF30))

Format as required

[Glenn Kennedy]

Hi Please re-read rule 1, which you agreed to stick to a few minutes ago. Your thread title is pretty useless. I've amended it for you, on this occasion as you're new here. It now looks like what we expect of thread titles.

[7ank0v1c]

Hi Please re-read rule 1, which you agreed to stick to a few minutes ago. Your thread title is pretty useless. I've amended it for you, on this occasion as you're new here. It now looks like what we expect of thread titles.

Sorry mate, I didnt know what to call it becasue it was complicated. Will do my best again in the future. Thanks for pointing out the error of my ways.

Select B20:AF20

Conditional Formatting
New Rule
Use a formula to determine...

=(B30:AF30=MAX($B30:$AF30))

Format as required

Thanks for your fast response, so i gave this a try both with MIN and MAX with different colours, however nothing happens at all. Any other ideas maybe?

[Glenn Kennedy]

Sorry mate, I didnt know what to call it becasue it was complicated. Will do my best again in the future. Thanks for pointing out the error of my ways.

To be honest, it took me a few moments to think of something better. Sometimes the words just don't come along in the right order!!

[Glenn Kennedy]

Maybe this: (adjust for your wider ranges)

=COLUMN()=MATCH(MAX($B$30:$K$30),30:30,0)

[7ank0v1c]

Maybe this: (adjust for your wider ranges)

=COLUMN()=MATCH(MAX($B$30:$K$30),30:30,0)

So I tried it and downloaded the file to see and it seems to be what I'm looking for (I adjusted it to the range I'm looking for), but it won't work again? not sure why. This is the formula I ended up using:

=COLUMN()=MATCH(MAX($B$30:$AF$30),30:30,0)

is this right?

[Glenn Kennedy]

Open the CF formula box and make sure:

a) that it does not have " " around the formula and/or

b) that it has not corrupted the ranges

CF messes up sometimes.

[7ank0v1c]

Ah okay, so I figured out its because I have corruptions in my range. However, I intentionally leave somethings things blank on my document and it causes these corruptions. How can I ignore these corruptions in this range and the formula still work?

[Glenn Kennedy]

It's not blank cells in a file that causes these problems. It's the way you copy/paste into the CF formula box. I'm sure there's a pattern to the corruptions, but it sure evades me!!! It's a constant source of annoyance to me.

Anyhow. It works for you now... doesn't it??

If so, you're welcome. if not. Post a sample sheet and shout.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

[7ank0v1c]

It's not blank cells in a file that causes these problems. It's the way you copy/paste into the CF formula box. I'm sure there's a pattern to the corruptions, but it sure evades me!!! It's a constant source of annoyance to me.

Anyhow. It works for you now... doesn't it??

If so, you're welcome. if not. Post a sample sheet and shout.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

Heya, sorry just one more thing within this, how can I do the exact same formula but also with ignoring cells with 0?

thanks a bunch so far, its been a massive help