Conditional formatting and specific formula

**bigbonusprize** · 02-23-2011, 10:06 AM

Hi,

How can I do a conditional formatting of cells C5:C9 if
C5 is not blank or contain some port of specific text.

Thank you for you help!

**NBVC** · 02-23-2011, 10:10 AM

Do you mean if
C5 is not blank and contain some part of specific text.?

and if so, what does "if
C5 is not blank or contain some part of specific text." mean?

**bigbonusprize** · 02-23-2011, 10:25 AM

Sorry I was not clear at all. These are two different conditions.
I mean If C5 contains the following part:
"sla" then format C4:C9 in blue
and
If C5 is not blank then format C4:C9 in red.

How can I do this?

**ChemistB** · 02-23-2011, 10:33 AM

Assuming blue takes priority over red (since in both cases, C5 is not blank)

Select your range, Conditional Formatting> Using Formula
=ISNUMBER(SEARCH("sla",C$4)) Format blue
Second condition
=C$4<>""
Do they work for you?

**arthurbr** · 02-23-2011, 10:36 AM

Chemist,
I think the OP meant to test C5 not C4 =ISNUMBER(SEARCH("sla",C$5)) and =C$5<>""

**bigbonusprize** · 02-23-2011, 11:15 AM

With the second condition:
=C$4<>""
It formats only the cell, which is not blank

**NBVC** · 02-23-2011, 11:41 AM

Which cell(s) is it supposed to format then? You said: If C5 is not blank then format C4:C9 in red. (there is still confusion as to whether we are looking at C5 or C4, but essentially chemist's formula says to format all cells in C4:C9 if C5 is not blank).

Maybe posting a small sample workbook might help to understand what you really want.

**bigbonusprize** · 02-23-2011, 11:48 AM

=ISNUMBER(SEARCH("Button",$D$6))

=C$6<>""

Both applies to
=$C$6:$F$6

**NBVC** · 02-23-2011, 11:56 AM

Here you are looking at 2 different cells for determining what to colour the range with, C6 and D6.

If you have the conditions listed in the order you show, then all cells in C6:F6 will be blue if the word "button" appears in D6. Stop.

If the first condition is not true, then it will look at C6 to see if it is blank. If C6 is not blank, then C6:F6 will all be red.

**bigbonusprize** · 02-23-2011, 12:03 PM

OK.
How can I change it in order to work as I want -
If button is met in D6, then - format the row in red.
But if buttons is not met in D6, and C6 is not blank, then format in blue

**NBVC** · 02-23-2011, 12:45 PM

Try to change second format to:

=AND(C$6<>"",ISERR(SEARCH("Button",$D$6)))

**bigbonusprize** · 02-24-2011, 12:01 PM

Doesn't work

Do you have any other suggestions?

**NBVC** · 02-24-2011, 12:03 PM

Yeah, upload a sample showing what you need to see.

**bigbonusprize** · 02-24-2011, 12:13 PM

http://img251.imageshack.us/i/40270780.jpg/

**NBVC** · 02-24-2011, 12:20 PM

An Excel spreadsheet attachment is preferred. I don't see row/column headers, etc...

**bigbonusprize** · 02-24-2011, 12:30 PM

Attached.
On the image is the final target formatting.
Thanks.

**NBVC** · 02-24-2011, 12:50 PM

See attached

**bigbonusprize** · 02-25-2011, 04:37 AM

OK Thank you for the help and support. Seems that the third rule was the problem.

**NBVC** · 02-25-2011, 09:24 AM

There were other problems.. if you look carefully... your original had $ before the row numbers.. so they were not applicable to each row independently, also, the Applied to range wasn't right... and the formula was referencing one row below the active row...