Auto Cell Color

[Guest]

Hello.
I I keep track of my bowling teams statistics. I type the scores in
columns C thru E rows 3 to 34. I have the formula for average in column H3
thru H34. When I type the scores in, I would like the color of the text to
be red for scores under average, black for scores from the average to 199,
and green from 200 and above. Is there anyway to do this automatically? I
am a new user.
Thank you very much.

 

[Chip Pearson]

Take a look at Conditional Formatting on the Format menu.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Guest]

Yes, I looked at that and tried a few things but couldn't figure out the
formula. Is there a place to learn about this?
Thank you.

 

[Dave Peterson]

You could start with Excel's Help
or Debra Dalgleish's site:
http://www.contextures.com/xlCondFormat01.html

 

[Guest]

Thanks very much. I think I've got it. I was able to do it for one row at a
time. I couldn't figure out how to all rows at the same time.

 

[Dave Peterson]

If you select the whole range, you can write your format|conditional formatting
formula based on the activecell.

Excel will adjust the range--just like it does when you copy a formula down a
column.

If you select A1:Z99 (say)
and use this as your conditional formatting formula:
=$a1>6

It'll adjust the row for each row in that range (and won't adjust the column).

 

[Guest]

Thank you, Thank you, thank you.
I selected cells C4 thru E34, I used condition 1, cell value is-greater
than-199,format green
condition 2, formula is C4<H3,format red
anything else is black.
worked like a charm.

 

[David McRitchie]

Yes but it would be better to select the cells that you want to be affected first
i.e. entire columns, rather than coping formatting afterwards.
http://www.mvps.org/dmcritchie/excel/condfmt.htm

 

[Guest]

Yes, I played around with it some more and what I ended up doing is selecting
all the cells and for condition 1: Formula is - =C3<ROUNDDOWN($H2,0) - format
red and condition 2: Cell Value is - >= 200 - format green.

Thank you very much for the reply.

 

[David McRitchie]

and that was with C3 as your active cell when you entered the formula, right,
because the active cell is the key to formula usage.