2003 excel Conditional Formatting dilemna - please help.

Forumla Testing sheet.xls

Hello. I'm new to this forum, but not new to excel (not experienced either though).

I'm having an issue with Conditional Formatting.

I'd like to apply 5 rules to 4 columns in my 2003 excel sheet (if 2003 will only allow 3, I will settle for 3 rules).

Columns: M, N, P, Q
If cell value is between:
150-244 turn cell to RED
245-299 turn cell to ORANGE
300-349 turn cell to YELLOW
350-400 turn cell to Bright GREEN
401-600 turn cell to Dark GREEN

I want to cells to remain that color even if sorted differently. No matter which cell the number 248 lands in it will always be orange.

I'd greatly appreciate any help with this. I have a short deadline, and I've tried multiple ways and can't figure it out. Thank you in advance!

It is true that Excel 2003 only allows 3 conditional formats per cell, but John McGimpsey describes a method of getting up to 6 in a cell, here:

http://www.mcgimpsey.com/excel/conditional6.html

The other 3 are obtained using Custom Formats, and will only work for numbers (so you're in luck).

Hope this helps.

Pete

Thanks Pete, but I still don't know how to use in between two numbers. It only lets me pick a cell to get in between, but I want to use actual numbers.
All cells with a value between 150 and 244 will be Red. I am unable to type in 150 or 244, it insists that I pick a cell. But if I pick a cell that is around the 150 mark it won't remain consistent once I sort the sheet differently because that cell that I clicked on would have changed value in the re-sort. This is why I need to input actual numbers, so that no matter what cell the re-sort moves the value to it will still be the correct color.

Do you know what I mean?

Instead of choosing Cell Value Is in the CF dialogue box, you should choose Use a Formula, and then enter this formula for the first condition:

=AND(A1>=150,A1<=244)

then click the Format button and choose Red, then OK your way out. Obviously, you can change A1 to suit the cell that you have selected, then subsequently use the Format Painter to apply the conditions to other cells (or select them all in the first instance). Repeat this for two other conditions, choosing the appropriate colours. Then use John's method for the final two.

Hope this helps.

Pete