Formatting the Hi and Low numbers in range

[Diamonelle]

Can anyone help me please. I am trying to find a way to make the cells containing the highgest and lowest price become a different color.
I'm not sure if I can make a macro, I cant do it with conditional formatting because the range of numbers are different in every column, therefore i would need to have a different rule for each column.

I need this ASAP for work... thanks a lot!

[Dav]

Why can you not use conditional formating, the ranges can be too big if the length varies and as long as there is no data in the other cells they will work. Or if you attached a zipped copy of the spreadsheet we coould be cleverer in defining a range.

Regards

Dav

[Diamonelle]

I'm attaching the file as a pdf, the highlited columes (Average) are the ones I need to have pick a high and a low, as you can see they are already highlited to reflect the high and low values. The problem is, there are 15 other files with ranges that differ. I have no way of knowing what the low may be later on, so therefore it's impossible to put a range in the conditional formatting. I hope you understand what I mean.. Can't wait to find out what the solution is..

[Maistrye]

Do you mean the number of elements in the range differ, or the values in the range differ?

These are two very different things.

If it's just the values in the range differing, you can use conditional formatting quite easily.

Simply highlight the whole section, say B2:H24.

For the following, it assumes your first column is B, your first row is 2, and your last row is 24. If these are different, you'll have to adjust the formulas.

For condition 1, (the max) put the conditional format with the following formula:
=IF(B$2=MAX(B$2:B$24),TRUE,FALSE)

For condition 2, (the min) put the conditional format with the following formula:
=IF(B$2=MIN(B$2:B$24),TRUE,FALSE)

If the number of elements in each differs, it will be more complicated, but still doable with formulas.

Scott

[Dav]

I would nearly agree with Scott's answer, Sleect the first range you are interested in (it can be longer than the range if there are no numberic values above of below the maximum) go to format, conditional formating and choose formula is then.

For condition 1, (the max) put the conditional format with the following formula:
=IF(B$2=MAX(B$2:B$24),TRUE,FALSE)

is what Scott gave but he put in an extra$ after the first B this would stop it copying down correctly. It can alos be simplified to
=b2=(max(b$2:B$24)
the range is obviously the range that you have the cells in you wish to be evaluated. Then choose a suitable colour.


For condition 2, (the min) put the conditional format with the following formula:
=B2=MIN(B$2:B$24)

If you select one of you conditionally formated cells you can paste special formats to other cells you wish to apply it to

If some of the ranges are lower down the sheet, you would have to give us a better example preferably as a zipped xls file, as the $ prevent the ranges changing when copied down, but their are other ways around this, copying the formulas manually is one and then editing them.

Let me know how you are progressing

Regards

Dav

[Maistrye]

Thanks for catching that Dav. It would have produced a hard to find error, that is for sure. :-)

Scott