conditionally changing cell color based upon value

With Excel 2003...

1) is it possible to change the cell color to yello for the top 10 percent
of cell values in a column? I have a column of numbers and I want the
biggest 10 percent of those numbers to stand out? I don't want to play
with conditional formatting "greater than" to find the >X value that will
make just 10 percent of the cells change to yellow?

2) is it possible to change the color of the next cell over? Instead of
those yellow cells being yellow on the value, I would prefer to make the
item name (like a product name -- text) be yellow even though the cell to
its right is what is being evaluated?

Do conditional formatting for the cell whose color you want to change.
By default conditions are based on cell value ("Cell Value Is", but you
can click on the arrow after the first field and change that to a
formula based ("Formula Is") condition to respond to values in other cells.

Jerry

AFN wrote:

> With Excel 2003...
>
> 1) is it possible to change the cell color to yello for the top 10 percent
> of cell values in a column? I have a column of numbers and I want the
> biggest 10 percent of those numbers to stand out? I don't want to play
> with conditional formatting "greater than" to find the >X value that will
> make just 10 percent of the cells change to yellow?
>
> 2) is it possible to change the color of the next cell over? Instead of
> those yellow cells being yellow on the value, I would prefer to make the
> item name (like a product name -- text) be yellow even though the cell to
> its right is what is being evaluated?

Thanks, but how do you specify that, say, the other cell, F4 is > 2 ?

What is the formula for if F4 > 2 ? I don't get the desired syntax that
it wants.


"Jerry W. Lewis" <post_a_reply@no_e-mail.com> wrote in message
news:41FD8106.5010903@no_e-mail.com...
> Do conditional formatting for the cell whose color you want to change.
> By default conditions are based on cell value ("Cell Value Is", but you
> can click on the arrow after the first field and change that to a
> formula based ("Formula Is") condition to respond to values in other
cells.
>
> Jerry
>
> AFN wrote:
>
> > With Excel 2003...
> >
> > 1) is it possible to change the cell color to yello for the top 10
percent
> > of cell values in a column? I have a column of numbers and I want the
> > biggest 10 percent of those numbers to stand out? I don't want to play
> > with conditional formatting "greater than" to find the >X value that
will
> > make just 10 percent of the cells change to yellow?
> >
> > 2) is it possible to change the color of the next cell over? Instead
of
> > those yellow cells being yellow on the value, I would prefer to make the
> > item name (like a product name -- text) be yellow even though the cell
to
> > its right is what is being evaluated?
>

Suppose E4 is the cell which has the text whose color you want to change based on the value in cell F4. Select cell E4, go to conditional formatting, Select the FORMULA IS as mentioned in the earlier post and enter the following in the box:
=F4>2
Select the desired format and click ok.

- Mangesh

AFN, use Jerry's and use this formula =$F$4>2

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

"AFN" <[email protected]> wrote in message
news:[email protected]...
> Thanks, but how do you specify that, say, the other cell, F4 is > 2 ?
>
> What is the formula for if F4 > 2 ? I don't get the desired syntax
that
> it wants.
>
>
> "Jerry W. Lewis" <post_a_reply@no_e-mail.com> wrote in message
> news:41FD8106.5010903@no_e-mail.com...
> > Do conditional formatting for the cell whose color you want to change.
> > By default conditions are based on cell value ("Cell Value Is", but you
> > can click on the arrow after the first field and change that to a
> > formula based ("Formula Is") condition to respond to values in other
> cells.
> >
> > Jerry
> >
> > AFN wrote:
> >
> > > With Excel 2003...
> > >
> > > 1) is it possible to change the cell color to yello for the top 10
> percent
> > > of cell values in a column? I have a column of numbers and I want
the
> > > biggest 10 percent of those numbers to stand out? I don't want to
play
> > > with conditional formatting "greater than" to find the >X value that
> will
> > > make just 10 percent of the cells change to yellow?
> > >
> > > 2) is it possible to change the color of the next cell over? Instead
> of
> > > those yellow cells being yellow on the value, I would prefer to make
the
> > > item name (like a product name -- text) be yellow even though the cell
> to
> > > its right is what is being evaluated?
> >
>
>

Perhaps a simple experiment (closely related to your=20
original post) to get the hang of using conditional=20
formatting (CF) to format cells in a col based on the=20
results in another col ?
=20
Assume the sample data below is in A2:B6

Name1 4
Name2 1
Name3 5
Name4 2
Name5 3

and we want the 3 names in A2:A6 for which the=20
corresponding number in B2:B6 are amongst the Top* 3 to be=20
highlighted in say, light brown fill
*highest values

Put in C2: =3DNOT(ISNA(MATCH(B2,LARGE($B$2:$B$6,{1;2;3}),0)))
Copy down to C6=20

Col C will evaluate to TRUE or FALSE, depending on whether=20
the number in col B is amongst the top 3
(Hide away col C if desired)

Select A2:A6
Click Format > Conditional Formatting
Under Condition 1, make the settings:
Formula Is | =3DC2
Click Format button > Patterns > Light brown > OK
Click OK at the main dialog

You'll see that Name1, Name3 and Name5 (with the highest 3=20
numbers: 3,4,5 in col B) are the cells conditionally=20
formatted with light brown fill

Note that we could normally dispense with the helper col=20
set-up and just plug the CF formula directly into=20
the "Formula Is" box. But in this case it's not possible=20
as array constants (the "{1;2;3}" part) are not allowed in=20
CF criteria.=20

Hope that helps ..

--=20
Rgds
Max =20
xl 97=20
---
GMT+8, 1=B0 22' N 103=B0 45' E
xdemechanik <at>yahoo<dot>com
----

"AFN" <[email protected]>=20
wrote in message news:0mhLd.4850
[email protected]...
> Thanks, but how do you specify that, say, the other=20
cell, F4 is > 2 ?
>=20
> What is the formula for if F4 > 2 ? I don't get the=20
desired syntax that
> it wants.

thank you!

"Max" <[email protected]> wrote in message
news:[email protected]...
Perhaps a simple experiment (closely related to your
original post) to get the hang of using conditional
formatting (CF) to format cells in a col based on the
results in another col ?

Assume the sample data below is in A2:B6

Name1 4
Name2 1
Name3 5
Name4 2
Name5 3

and we want the 3 names in A2:A6 for which the
corresponding number in B2:B6 are amongst the Top* 3 to be
highlighted in say, light brown fill
*highest values

Put in C2: =NOT(ISNA(MATCH(B2,LARGE($B$2:$B$6,{1;2;3}),0)))
Copy down to C6

Col C will evaluate to TRUE or FALSE, depending on whether
the number in col B is amongst the top 3
(Hide away col C if desired)

Select A2:A6
Click Format > Conditional Formatting
Under Condition 1, make the settings:
Formula Is | =C2
Click Format button > Patterns > Light brown > OK
Click OK at the main dialog

You'll see that Name1, Name3 and Name5 (with the highest 3
numbers: 3,4,5 in col B) are the cells conditionally
formatted with light brown fill

Note that we could normally dispense with the helper col
set-up and just plug the CF formula directly into
the "Formula Is" box. But in this case it's not possible
as array constants (the "{1;2;3}" part) are not allowed in
CF criteria.

Hope that helps ..

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

"AFN" <[email protected]>
wrote in message news:0mhLd.4850
[email protected]...
> Thanks, but how do you specify that, say, the other
cell, F4 is > 2 ?
>
> What is the formula for if F4 > 2 ? I don't get the
desired syntax that
> it wants.

"AFN" wrote:
> thank you!

You're welcome!
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----