how do i get a cell to fill red/blue as the outcome of a formula?

[Lynz1976]

I want to get a cell to fill in a particular colour to indicate if one of my
pupils is working ahead or on target. I want excell to execute a check and
fill the cell automatically with the appropriate colour.

 

[Pete_UK]

You would use Conditional Formatting for this. If you would like
further (more detailed) guidance then please tell us a bit about what
data you have, how it is laid out, and in what circumstances you want
the colour to change.

Pete

 

[Suleman Peerzade]

Hi,

Use conditional formatting

http://www.contextures.com/xlCondFormat01.html
--
_______________________
Click "Yes" if it helps
________
Thanks
Suleman Peerzade

 

[Lynz1976]

Hi I will be entering a grade into a cell ie Cc or Cb etc. This I will check
against the target grade for the pupil using an if statement or something
similar. This will then return a value which I would like to trigger the
colour of the cell. i.e. if the student its below target Red on target Yellow
ahead of target Green. Can you help with this?

Thanks Lyndsey

 

[Pete_UK]

I assume you will have pupils listed on different rows, with data
going across for each pupil. Which cell/column will hold the target
grade and which cell/column for the actual grade?

I presume you just want the actual grade cell to change colour?

Pete

 

[Lynz1976]

Yes each pupil will have their own row of information. Im not sure yet
exactly which cell will hold the target and which the current grade but lets
say for arguments sake the target grade cell is C2 and the current grade cell
is D2. Yes the cell that the current grade is in can change colour that will
be great.

Thanks Lyndsey

 

[Pete_UK]

Okay, well assume you have a header row, so that your first pupil's
data is in row 2. Highlight from cells D2 down to the bottom of your
data, then click on Format | Conditional Format - a panel pops up. In
the first box of this you should select Formula Is rather than Cell
Value Is, and then in the next box you should enter this formula:

=AND(D2<>"",D2>C2)

Then click on the Format button and select the Patterns tab (for
background colour) and choose Green, then click OK to get back to the
first dialogue box.

Click Add to set up the next condition, choose Formula Is again, and
enter this formula:

=AND(D2<>"",D2=C2)

This time click on the Format button, Patterns tab and choose Yellow
(for on target), then click OK.

Click Add once more to set up the final condition, Formula Is, and
enter this formula:

=AND(D2<>"",D2<C2)

This time you need to choose Red in the format boxes, then click OK
twice to remove the dialogues.

Excel will have adjusted the cell references to suit the range that
you had highlighted, so you can test it out by putting a value in one
of the C cells and vary values in the corresponding D cell.

Hope this helps.

Pete

 

[Lynz1976]

Thanks I will have a look

Hi,

Use conditional formatting

http://www.contextures.com/xlCondFormat01.html
--
_______________________
Click "Yes" if it helps
________
Thanks
Suleman Peerzade

 

[Lynz1976]

Thanks that was really help full I have it working for letter grades now. I
have another question if you dont mind?? I also have to do it for combined
number and letter grades i.e. 6c or 6b or 6a etc. I cant get it to work for
that? Do you know why and how I can make it work?

Many thanks Lyndsey

 

[Pete_UK]

Glad to hear that it worked for you.

What are the rules for these combined grades? Is 6a considered to be
better than 6b and 6c ? The problem with this is that c is a "larger"
letter than a, and so it is "greater than". There are ways of coping
with this, eg by converting those grades to a number - this number
doesn't really mean anything, but it just represents the sequence that
the grades should be taken in.

For this to work you will need to list all possible grades in the
appropriate sequence. Can you post them here, and I'll advise you
further tomorrow?

Pete

 

[Lynz1976]

They go 3c, 3b, 3a, 4c, 4b, 4a etc all the way up to 8a, a being higher than
c and 8a being the best 3c being the worst. Thanks for your help with this.

Lyndsey

 

[Pete_UK]

Good morning Lyndsey,

you obviously start earlier than I do !!

To make the formulae shorter, I would suggest that you put those
grades in the correct sequence (lowest first) in an out of the way
part of your sheet. I put these in Z1:Z18:

3c
3b
3a
4c
4b
4a
5c
5b
5a
6c
6b
6a
7c
7b
7a
8c
8b
8a

Then I highlighted this range and clicked on Insert | Names | Define
and then named this range "grades" (without the quotes).

Then you need to go through the same procedure as before, but with
different formulae. So, highlight cells from D2 down to however many
you think you need, and click on Format | Conditional Formatting. Each
time you will need to select Formula Is, and this time your formula
will be:

=AND(D2<>"",MATCH(D2,grades,0)>MATCH(C2,grades,0))

for the green condition:

=AND(D2<>"",D2=C2)

for the yellow condition (unchanged), and:

=AND(D2<>"",MATCH(D2,grades,0)<MATCH(C2,grades,0))

for the red condition.

Click OK twice, and then test it out by putting, say, 4b in C2 and
then varying D2.

Hope this helps.

Pete

 

[Lynz1976]

Sorry I only just got back onto this again got side tracked!! Thats worked
really well thank you for all your help.

Lyndsey

 

[Lynz1976]

Sorry I only just got back onto this I got side tracked!! Thats worked really
well thanks for all your help.

Lyndsey