Excel formulas and shading

[hypernova]

This has nothing to do with arcades, but at work, I've put together a spreadsheet with various rates from many trucking companies we use/have used/may use in the future.  There's a total of around 8 or 9 on the spreadsheet.  What I've done, is take a single class and weight, and got the rate for numerous zip codes.  (Around 70.)  So there's around 600 or so fields.  Now a few of the companies only service certain parts of the nation, so it's really probably around 350-400 or so fields of data.

What I've done so far is take the lowest rate in each row, and shade it yellow.  I then took the next ones within $25 and shaded them red.  The ones within $50 were purple, and the ones within $75 were gray.  I also did this the hard way.  One by one.  This is quite the timesucker.  Now every so often, a new company wants in on some business, or our existing companies redo their discounts, and I have to go in, and change all the prices (not a big deal,) and ALSO potentially change all the color coding if they end up being the new minimum price in a certain zip.  (A big deal.  Lots of time analyzing, and changing colors, one after the other.)

So I know Excel has an option to automatically highlight and do things like I'm doing, but much faster, and when I change the prices, will update the colors automatically.  I started looking through the help section, but was unable to get the MIN working.  I did what it said for highlighting the minimum, but it kept highlighting the entire row instead of just the lowest price.  =MIN(E4:E12) was the coding it told me to use, which I did.  What happened?

Also, I figure I need to use "IF" formulas for the other fields, but not quite sure how.  Can anyone let me know how to do this right?  I need the minimum price in a row to be one color.  Anything within $25 another color, anything within $50 another color, and anything within $75 another color.  Also, if there's an option, to make blank fields another color (actually it'll be a pattern, but anywho...)  Thanks!

[nobbo]

if you type the formula =MIN(E4:E12) into a completely seperate cell, it should then display the minimum value out of the range E4 - E12. That has absolutely nothing to do with changing the colour of the cell.

In short, it would be quite a pain in the butt to try and do what you want to do, without trying to code a macro, which would then involve scripting etc...

i do not know exactly how proficient you are with programming, but if you are do a search for "excel macro code" and you should come up with a few helpful sites

[albrigsr]

You should be able to achieve this using a conditional format uner the Format Menu.  I have attached a picture of the sample formula that can be extended to any amount to continue to add $25 increments.  I also used the min command to arrive at the lowest number in the row and then use tha as my base before incrementing by $25.  I think this answers your question.