Say if we have the names of students in Column A of the
excel spreadsheet and the description of their grades in Column B as follows:
“Excellent”, “Pass” or “Fail”. You want the names of the students in Column A
to appear “Yellow” if their grades are “Excellent”, “Green” as “Pass” and “Red” as “Fail”. This is what you
do:
Open up Microsoft Excel 2007
Select the cell containing the first name entry, in this
case it is A1
With cell A1 selected, go to “Home”->”Conditioning
Formatting”->”New Rule”->”Use a Formula to determine which cells for
format”
Inside the bar under “Format values where this formula is
true”, type =(B1=”Excellent”) and
click the “Format” button below and choose the “Yellow” colour, click Ok and Ok.
STILL with cell
A1 selected, go to “Home”->”Conditioning Formatting”->”New Rule”->”Use
a Formula to determine which cells for format”
Inside the bar under “Format values where this formula is
true”, type =(B1=”Pass”) and click
the “Format” button below and choose the “Green”
colour, click Ok and Ok.
STILL with cell
A1 selected, go to “Home”->”Conditioning Formatting”->”New Rule”->”Use
a Formula to determine which cells for format”
Inside the bar under “Format values where this formula is
true”, type =(B1=”Fail”) and click
the “Format” button below and choose the “Red”
colour, click Ok and Ok.
To apply this formula to other names in Column A, select
cell A1, select “Format Painter” and paint the brush over the other cells in
Column A.
Note: this method should work well unless the cell reference
becomes locked like B1 becomes $B1$1