conditional formatting

[Graham Warren]

Trying to set up a spreadsheet to colour test scores of students different
colours based on a target score column.
If score is greater than the target column then i want it to colour it
green, if same as the score then orange, and less than the score, red.
Unfortunately I've got 300 pupils all with different 'targets' and I can't
figure out a way to set the conditional formatting to a 'cell value' (e.g
'C2') rather than a number (eg '3'). I've tried sorting all children in to
target order then conditional formatting manually the whole lot. But when I
re-order children alphabetically all the formatting is 'lost'.

Any help very much appreciated. I can put what I've got so far as a link
if anyone wants some data to play with. Just let me know.

Many thanks. Graham.

 

[Bob Phillips]

Graham,

You need to select all your target cells, and use a formula like

=A1<C2

then

=A1<C3

etc.

Also take a look at http://www.xldynamic.com/source/xld.CF.html#top3, it
might help.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Debra Dalgleish]

Assuming your text scores are in column B, and Target scores are in
column C, rows 2 to 50 --

Select cells B2:B50 (B2 is the active cell)
Choose Format>Conditional Formatting
From the first dropdown, choose Formula Is
In the text box, type: =B2>C2
Click the Format button, and on the Patterns tab, choose the Green colour
Click OK
Click the Add button

For Condition 2, from the first dropdown, choose Formula Is
In the text box, type: =B2=C2
Click the Format button, and on the Patterns tab, choose the Orange colour
Click OK
Click the Add button

For Condition 3, from the first dropdown, choose Formula Is
In the text box, type: =B2<C2
Click the Format button, and on the Patterns tab, choose the Red colour
Click OK, click OK