conditionally changing cell color based upon value

  • Thread starter Thread starter AFN
  • Start date Start date
A

AFN

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
 
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, 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
 
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 ..
 
thank you!

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 ..
 
Back
Top