Condition formatting

**ExcelBG** · 04-02-2013, 10:13 AM

Could u help me again...

I want to create a conditional formatting but this time I have:

A
1 John
2.Rado
3.Simo

...so I want to highlight cells which contain Rado and Simo

Thanks in advance...

**Jonmo1** · 04-02-2013, 10:17 AM

Originally Posted by ExcelBG

...so I want to highlight cells which contain Rado and Simo

Do you mean
so I want to highlight cells which contain Rado OR Simo

A cell cannot be BOTH Rado AND Simo..

So try using the OR function instead of AND in your conditional formatting rule.

**benishiryo** · 04-02-2013, 10:19 AM

hi ExcelBG. when we use "contain", it usually means the full text can be "A Rado B" & it will highlight it. so i think you probably meant it as "equals to"? select the range you want to apply to (eg A1:A10)
go to Home -> Conditional Formatting -> New Rule -> Use a formula to determine which cells to format -> Format values where this formula is true:
=OR(A1={"Rado","Simo"})

if you really meant it as "contains", then:
=SUM(COUNTIF(A1:A1,{"*Rado*","*Simo*"}))

**Jonmo1** · 04-02-2013, 10:27 AM

Originally Posted by benishiryo

=OR(A1={"Rado","Simo"})

if you really meant it as "contains", then:
=SUM(COUNTIF(A1:A1,{"*Rado*","*Simo*"}))

I don't think you can use constant arrays {"Rado", "Simo"} in conditional formatting formulas.
You would have to write out the entire or
=OR(A1="Rado",A1="Simo")

Or a COUNTIF+COUNTIF instead of SUM(COUNTIF

**benishiryo** · 04-02-2013, 10:34 AM

@Jonmo1:
ahh you're right. didn't try that out. 2nd one would be:
=COUNTIF(A1:A1,"*Rado*")+COUNTIF(A1:A1,"*Simo*")

or:
=OR(ISNUMBER(SEARCH("Rado",A1)),ISNUMBER(SEARCH("Simo",A1)))