Multiple Condtional Formating

Is it possible to have 2 sets of CF for a cell or range based upon the text of another cell with a validation list?

I need to RAG a cell on values between 1 to 14 or 1 to 7 depending on whether another cell equals "PERM or "PROB". Essentially I suppose I need 6 conditions. I assume it will be a Formula is formatting rule?

what are the required conditions exactly can you list them?

C2 is the cell I wish to format and F2 is the cell with the validation list. If F2 = "PERM" then the value 1-6 will return GREEN, 7-14 AMBER & >14 RED. IF F2="PROB" then 1-3 will be GREEN, 4-7 AMBER & >7 RED.

You can do that like this, using "formula is" option in CF

condition 1
=C2>IF(F2="Perm",14,IF(F2="Prob",7,C2))
format red

condition 2
=C2>IF(F2="Perm",6,IF(F2="Prob",3,C2))
format amber

condition 3
=OR(F2="PERM",F2="Prob")*ISNUMBER(C2)*(C2>=1)
format green

Originally Posted by daddylonglegs

You can do that like this, using "formula is" option in CF

condition 1
=C2>IF(F2="Perm",14,IF(F2="Prob",7,C2))
format red

condition 2
=C2>IF(F2="Perm",6,IF(F2="Prob",3,C2))
format amber

condition 3
=OR(F2="PERM",F2="Prob")*ISNUMBER(C2)*(C2>=1)
format green

Thanks for the reply, however this does not seem to be working? Infact Excel is advising there is an error somewhere? Can anyone help?

Could you post a small sample of your data?
Which error do you get?

Been working on other stuff recently but now need to finish this! Here is the sample data if anyone can help: -

Sickness Log.xls

perm/prob is in P10
c2 isn't used
where are the values 1-14 going then?

Assuming you want to format I14:I35 based on the values in that column and the value in P10 you can revise my suggestion above.

Select the whole range I14:I35 with I14 "active cell" then apply formatting as follows using "formula is":

condition 1
=I14>IF(P$10="Perm",14,IF(P$10="Prob",7,I14))
format red

condition 2
=I14>IF(P$10="Perm",6,IF(P$10="Prob",3,I14))
format amber

condition 3
=OR(P$10="PERM",P$10="Prob")*ISNUMBER(I14)*(I14>=1)
format green