Conditioning formating a data value to change to a color

[steve]

Hello,
What I am trying to do is lets say I have a bunch of numbers in column F
(for illustration purposes I will only show one number) and what I would
like if any of the numbers in column F are greater than 300 I want it to
change the color of that number to red automatical (See example)
date time day evening night 24hr. Totals
3/12/04 8:00 100
12:00 300
4:00 600

1000 (change to red)
I have no problem with changing the number to red manually but it gets to be
a pain in the neck when the spreadsheet gets really long.
Any help to this would be appreciated.

 

[DDM]

Steve, highlight Column F (click the column heading at the top of the
worksheet), then pull down the Format menu and select Conditional
Formatting. Your condition is "Cell value is greater than 300." Click the
Format button, select the Font tab, select red for the font color, then
click OK, and OK again.

 

[steve]

Hello,
Thanks for your reply. I just wanted to say that it works but there is one
minor problem which I forgot to explain. In my example:
date time day evening
night 24hr. total (no red)
3/15/04 8:00
100
3/15/04 12:00 300
3/15/04 4:00 600

1000 (red)
date time day evening
night 24hr. total (no red)
3/16/04 8:00
100
3/16/04 12:00 300
3/16/04 4:00 600

1000 (red)
I don't want any of the column headings (24hrs. total to be included in
red). I only want the numbers.

 

[Max]

2 ways you can try:

A. Remove the conditional formatting [CF] for the headers

Select any empty cell (which is without the CF)

*Double-click* on the Format Painter button ("brush" icon)

Now just click on each of the header cells in col F in turn
to "paint" over and remove the CF

When done, press Esc to revert the cursor to normal

B. Change the conditional formatting [CF] to one using "formulas" instead

Select col F

Click Format > Conditional Formatting

Under "Condition 1", make the settings:
Formula Is | =AND(ISNUMBER(F1),F1>300)

[The previous formatting you applied - font>bold red? - should still be
there
Otherwise, just format to taste]

Click OK

The CF formula will ensure that all your column headings ("24hr. total")
in col F, which are *not* numbers, will remain "unformatted"

 

[David McRitchie]

Comments on removal of C.F. from rows (Max's suggestion A):
One of the problems of C.F. is knowing the extent of the
C.F. and trying to guess what cells were originally selected
when the C.F. was created. If you choose entire columns
or the entire worksheet then you can correctly guess the extent.

Making changes to C.F.:
I don't know of any way to select a cell and extend the
selection to other cells that have the same C.F. If this were
possible it would be a lot easier to work with C.F.

If you removed the conditional formatting for cells at the top
you will have to reselect the original C.F. and then move
with the arrow keys to the proper cell one row down, modify
the formula or formatting and then remove the C.F. from your
header row again. One way to avoid that is to include additional
code in the C.F. for exclusion or make sure that the formulas only
apply to certain kinds of data in order to reduce the scope within the
formula.

Changes to Formulas:
So my preference would be to change the formulas as in
Max's second suggestion (B).

My page on Conditional Formatting is:
http://www.mvps.org/dmcritchie/excel/condfmt.htm
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

2 ways you can try:

A. Remove the conditional formatting [CF] for the headers

Select any empty cell (which is without the CF)

*Double-click* on the Format Painter button ("brush" icon)

Now just click on each of the header cells in col F in turn
to "paint" over and remove the CF

When done, press Esc to revert the cursor to normal

B. Change the conditional formatting [CF] to one using "formulas" instead

Select col F

Click Format > Conditional Formatting

Under "Condition 1", make the settings:
Formula Is | =AND(ISNUMBER(F1),F1>300)

[The previous formatting you applied - font>bold red? - should still be
there
Otherwise, just format to taste]

Click OK

The CF formula will ensure that all your column headings ("24hr. total")
in col F, which are *not* numbers, will remain "unformatted"

--
Rgds
Max
xl 97
---
Please respond, in newsgroup
xdemechanik <at>yahoo<dot>com
----

Hello,
Thanks for your reply. I just wanted to say that it works but there is one
minor problem which I forgot to explain. In my example:
date time day evening
night 24hr. total (no red)
3/15/04 8:00
100
3/15/04 12:00 300
3/15/04 4:00 600

1000 (red)
date time day evening
night 24hr. total (no red)
3/16/04 8:00
100
3/16/04 12:00 300
3/16/04 4:00 600

1000 (red)
I don't want any of the column headings (24hrs. total to be included in
red). I only want the numbers.

 

[Max]

Thanks for insights, Dave !