Color Help Please

[YMC]

i am running excel 2000 (yah its a little old but gets the job done) ok here
is my question. lets say i have a whole list of numbers in a unknown amout of
cells in a column. for example: 357,901 357,902 and 357,903. how would i make
the highest number (357,903) turn red, Also how would i make lowest number
(357,901) turn blue? thankyou in advance

 

[Gary''s Student]

Use conditional formatting with Formula Is something like:

=A1=MAX(A:A) and pick your color
then copy down th column


Repeat this with MIN and a new color

 

[HKaplan]

Assuming your list is in cells A1:A3, use this formula in Format |
Condition Formatting:

=MAX(A3:A5)=A3 would return Green (the color I specifiy for highest)
if cell A3 was the highest of cells A1 : A3.

=MIN(A3:A5)=A1 would return Red (the color I specifiy for lowest) if
cell A1 was the highest of cells A1 : A3.

You can create this conditional formula for the first cell in the
series, then paste the formatting (using the format painter icon) down
the list. Make sure your current cell reference is relative, meaning
=MAX(A3:A5)=A3, not =MAX(A3:A5)=$A$3.

 

[YMC]

that was just an example. i will go into detail a little bit more. basicly
what is happening is that data is being transfered into my excel sheet from a
3rd party app. so my problem is that i really have no idea of how much
information will be put into the sheet. the formula u use works but, not
knowing the amount of data that could go onto the sheet is my main problem
right now. it could range from 1 cell of data to 30,000+. so therefore the
method that u have used is more or less combersome approach in my situation.
if excel cant do this method of coloring than what is my next option, vbcode,
macros? any kind of help or an idea is greatly appreciated. thankyou

 

[HKaplan]

You can open one sheet with data and record/save a macro to automate
future sheets.
Step1 - Put the cursor on the first cell of data
Step 2 - Start your macro recorder
Step 3 - Create the conditional formatting for the first cell
Step 4 - click the format painter icon
Step 5 - click Shift+Ctrl+Down Arrow.
Step 6 - Click Ctrl+Home
Step 7 - Stop the macro.

Open a new set of data and run the macro. This should work.

 

[Gary''s Student]

It does not matter how much data has been pasted into column A. The
conditional formula will handle the entire column!!


Just copy the conditional format from A1 and paste it ALL THE WAY DOWN.

 

[David Biddulph]

I think you intended =MIN(A$1:A$3)=A1 not =MIN(A3:A5)=A1 ?

Although the reference of the current cell needs to be relative, the
reference of the range needs to be absolute (which is presumably why you
ended up with A3:A5 when you copied down from A1 to A3).