Conditional Formatting

Hi

I want to change color based on the value in the range. -

First im not sure about what formula to use and also conditional formatting multiple color in the same rule - Please help me

e.g.

A B C Status
yes yes yes Green color
yes no yes Amber color
yes n/a yes Amber color
yes no n/a Amber color

Thanks
Thiru

Originally Posted by teylyn

Hello,

you need one rule for each color.

for amber: =COUNTIF($A1:$C1,"yes")<3
for green: =COUNTIF($A1:$C1,"yes")=3

Attachment 432147

If you want the color in a different column just apply the format to D1:D4 instead of A1:C4

cheers, teylyn

Hi Sir - Thank you very much for your reply - Few clarification required.

1. So we cannot use multiple colors in the same rule? - Is that right?
2. Is there any other to mention multiple criteria e.g. finding two or more criteria in a range
if(range = "criteria1" or range= "crtieria2" or range = "criteria3") - Something like this.

Thanks
Thiru

Originally Posted by bebo021999

For second CF in same cell, just stay at that cell, apply new rule for new condition. This second CF will be in 2nd priority.

Thank you

So only one rule for one color!

And do you got any idea regarding criteria i.e. Looking for a formula something like this if(range = "criteria1" or "crtieria2" or "criteria3"), dothis ?

Originally Posted by bebo021999

1) Yes, one rule for one color

2) Use :
=OR(range="criteria1",range="criteria2",...)

Thank you but OR function is not working for Range!!!

Thanks
Thiru

Originally Posted by bebo021999

Why not?

Or try:

=OR(range={"criteria1","criteria2"})

If it does not work, try to upload a sample worksheet.

No! it doesn't work - Here you go I have attached the sample filesample.xlsx

Originally Posted by teylyn

"range" is just a placeholder for a real cell address like A1. This should work

=or($a1="this",$a1="that",$a1="the other",$B1="this",$B1="that", ....etc)

You need to be careful with the $ sign and truly understand how relative and absolute references work.

Great guys teylyn and bebo021999 - Thank you very much!