Auto Cell Color

[Chuckers]

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


"Chuckers" <[email protected]> wrote in message
news:[email protected]...
> 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.

[Chuckers]

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.

"Chip Pearson" wrote:

> Take a look at Conditional Formatting on the Format menu.
>
>
> --
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel
> Pearson Software Consulting, LLC
> www.cpearson.com
>
>
> "Chuckers" <[email protected]> wrote in message
> news:[email protected]...
> > 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.
>
>
>

[Dave Peterson]

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

Chuckers wrote:
>
> 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.
>
> "Chip Pearson" wrote:
>
> > Take a look at Conditional Formatting on the Format menu.
> >
> >
> > --
> > Cordially,
> > Chip Pearson
> > Microsoft MVP - Excel
> > Pearson Software Consulting, LLC
> > www.cpearson.com
> >
> >
> > "Chuckers" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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.
> >
> >
> >

--

Dave Peterson

[Chuckers]

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" wrote:

> You could start with Excel's Help
> or Debra Dalgleish's site:
> http://www.contextures.com/xlCondFormat01.html
>
> Chuckers wrote:
> >
> > 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.
> >
> > "Chip Pearson" wrote:
> >
> > > Take a look at Conditional Formatting on the Format menu.
> > >
> > >
> > > --
> > > Cordially,
> > > Chip Pearson
> > > Microsoft MVP - Excel
> > > Pearson Software Consulting, LLC
> > > www.cpearson.com
> > >
> > >
> > > "Chuckers" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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.
> > >
> > >
> > >
>
> --
>
> Dave Peterson
>

[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).

Chuckers wrote:
>
> 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" wrote:
>
> > You could start with Excel's Help
> > or Debra Dalgleish's site:
> > http://www.contextures.com/xlCondFormat01.html
> >
> > Chuckers wrote:
> > >
> > > 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.
> > >
> > > "Chip Pearson" wrote:
> > >
> > > > Take a look at Conditional Formatting on the Format menu.
> > > >
> > > >
> > > > --
> > > > Cordially,
> > > > Chip Pearson
> > > > Microsoft MVP - Excel
> > > > Pearson Software Consulting, LLC
> > > > www.cpearson.com
> > > >
> > > >
> > > > "Chuckers" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > 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.
> > > >
> > > >
> > > >
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

[Chuckers]

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.

"Dave Peterson" wrote:

> 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).
>
> Chuckers wrote:
> >
> > 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" wrote:
> >
> > > You could start with Excel's Help
> > > or Debra Dalgleish's site:
> > > http://www.contextures.com/xlCondFormat01.html
> > >
> > > Chuckers wrote:
> > > >
> > > > 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.
> > > >
> > > > "Chip Pearson" wrote:
> > > >
> > > > > Take a look at Conditional Formatting on the Format menu.
> > > > >
> > > > >
> > > > > --
> > > > > Cordially,
> > > > > Chip Pearson
> > > > > Microsoft MVP - Excel
> > > > > Pearson Software Consulting, LLC
> > > > > www.cpearson.com
> > > > >
> > > > >
> > > > > "Chuckers" <[email protected]> wrote in message
> > > > > news:[email protected]...
> > > > > > 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.
> > > > >
> > > > >
> > > > >
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson
>

[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
---
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

"Chuckers" <[email protected]> wrote in message news:[email protected]...
> 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.

[Chuckers]

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" wrote:

> 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
> ---
> 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
>
> "Chuckers" <[email protected]> wrote in message news:[email protected]...
> > 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]

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.

"Chuckers" <[email protected]> wrote
> 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.