Quadrant colour based on cell value

Hi,

I need help with the conditional formatting of my document, it is designed to monitor training competences, so this formatting will be used many times within this document, so it needs to be simple to copy and paste if posible.

I have 4 cells (F2, G2, F3, G3) in a Quadrant and one cell (E2) that will have a number from 0 to 5 added to it by the user, no other number should be able to be added to this cell and therefore shouldn't produce a response.

If (E2) is 0 then Cells (F2, G2, F3, G3) are blank (No knowledge)
If (E2) is 1 then Cell (F2) is Green (Poor Knowledge)
If (E2) is 2 then Cell (F2, G2) are Green (Fair Knowledge)
If (E2) is 3 then Cell (F2, G2, F3) are Green (Good Knowledge)
If (E2) is 4 then Cell (F2, G2, F3, G3) is Green (Excellent Knowledge)

If (E2) is 5 then Cell (F2, G2, F3, G3) is Red (Trainer)

Thanks in advance for your help

in conditional format for
F2 put

E2 = 5 -- red
AND( E2 >=1 , E2<= 4) -- green

G2 put

G2 = 5 -- red
AND( E2 >=2 , E2<= 4) -- green

F3 put

E2 = 5 -- red
AND( E2 >=3 , E2<= 4) -- green

G3 put

E2 = 5 -- red
E2= 4 -- green

not sure how easy that will be to copy to apply to other cells

Hi,
Thanks for the reply, i have done what you suggest, but don't seem to get it to work,

I have selected Conditional formatting and then New Rule, i have then selected "Use a formula to determine which cells to format" and copied what you gave me into each cell, but nothing happens, am i doing something wrong.

no

goback and edit the formulas
just check that
""
are not added

=" ...... "
if so remove

It does add the =" " but when i try to remove i get an error saying the formula is wrong.

This is what is in the conditional formatting

="E2 = 5 -- red AND( E2 >=1 , E2<= 4) -- green"

And what i try to change it to is

E2 = 5 -- red AND( E2 >=1 , E2<= 4) -- green

when i press ok it changes it back to

="E2 = 5 -- red AND( E2 >=1 , E2<= 4) -- green"

sorry
they need to be separate rules

although Kevin UK
has replied now with an example

mine attached

in conditional format for
F2 put

New Rule
E2 = 5
format to be red
new rule
AND( E2 >=1 , E2<= 4)
format to green

Thanks everyone,

I understand why Etaf put them on separate lines now, all makes sense now

This just shows my limited knowledge on conditional formatting.

Thanks again to you all for your patience

Just one quick question,

I have approximately 60 rows to do, each having 4 quadrants each, making 8 rules per row.

And this will be used for each machine, which will be on a separate tab 40 + machines

Do i have to write the conditional formatting out 8 x 60 x 40 = 19200 rules in the document

Is there a way to copy and past the rules

are they all in the same format

in that the cell you are testing is in the cell left of the block

if so you should be able to copy the block

E2: G3
to anywhere and it work OK
as i have not setup any $ values

if not
is there any patten to the format

try it on my example

just select E2:G3 and copy anywhere or any sheet

Yes they are all in the same format.

But when i copy the cells down

all looks ok, but when i insert a figure into the top row it formats all of the copied cells not just the relevant row

So Row 1 to 60, if i copy down anything in row 1 will format all 60 rows

i take that back

you can copy
highlight
E2 to G3
in the bottom right corner - the curser will change to a bold +
and drag down
works ok for me

why are you copying row 1 to 60
if the formula is in E2?
2 to 60

Hi etaf

I was originally using Kevin UK's sample to test things out, as your original example did not function correctly (it required input on F2 and E3 to function) so i was using Kevin UK's method of doing things, now that i have a little more time i have downloaded you revised file (I didn't know you had revised it )

But yes your new sample works and can be copied as i need, the reason i am copying from row 1 is to allow a space in between each viable row, so row 1 copies as a blank row, keeping things neat.

Thanks for all your help.

kevin UK used absolute addressing with $ - hence why the copy would not work - take the $'s out and it would also work and copy