conditional formatting
 

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.


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.772 / Virus Database: 519 - Release Date: 01/10/2004

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)


"Graham Warren" wrote in message
...
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.


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.772 / Virus Database: 519 - Release Date: 01/10/2004

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 FormatConditional Formatting
From the first dropdown, choose Formula Is
In the text box, type: =B2C2
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

Graham Warren wrote:
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.

--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html