Conditional Formatting help required
 

I am trying to format the background color of a cell based on text in that
cell.

If the cell contains "EXCELLENT", I want to color the background green
(works great).

If the cell contains "PARTIAL", "GOOD", or "MEDIUM", I want to color it
yellow. I've tried putting the following in a Cell, but it just returns a
#Value error.

=OR(SEARCH("MEDIUM", D4) 0, SEARCH("PARTIAL",D4)0,Search("GOOD", D4)0)

When I only put one of these critiera in the OR function, it returns the
proper logical value. Any ideas what I am doing wrong?

Actually, I tested =SEARCH("GOOD", D4) and =Search("PARTIAL", D4)
individually as the cell forumla and they each returned a #Value! result.

Any ideas how to make this work?

--
Email address is not valid.
Please reply to newsgroup only.

Conditional Formatting help required
 

OK, so I figured out that the Search( ) function returns #Value! if it
doesn't find a the search string in the string to be searched (why can't it
return a zero like the INSTR function in ACCESS? Thank you Microsoft!)

So how do I work around this problem?
--
Email address is not valid.
Please reply to newsgroup only.


"Dale Fye" wrote:

I am trying to format the background color of a cell based on text in that
cell.

If the cell contains "EXCELLENT", I want to color the background green
(works great).

If the cell contains "PARTIAL", "GOOD", or "MEDIUM", I want to color it
yellow. I've tried putting the following in a Cell, but it just returns a
#Value error.

=OR(SEARCH("MEDIUM", D4) 0, SEARCH("PARTIAL",D4)0,Search("GOOD", D4)0)

When I only put one of these critiera in the OR function, it returns the
proper logical value. Any ideas what I am doing wrong?

Actually, I tested =SEARCH("GOOD", D4) and =Search("PARTIAL", D4)
individually as the cell forumla and they each returned a #Value! result.

Any ideas how to make this work?

--
Email address is not valid.
Please reply to newsgroup only.

Conditional Formatting help required
 

If the cell contains "PARTIAL", "GOOD", or "MEDIUM",

Are those words a *substring* or are they the only string?

If they're the only string:

=OR(D4="PARTIAL",D4="GOOD",D4="MEDIUM")

If those words are a *substring*:

=OR(ISNUMBER(SEARCH("partial",D4)),ISNUMBER(SEARCH (F2,"good")),ISNUMBER(SEARCH(F3,"medium")))

Biff

"Dale Fye" wrote in message
...
I am trying to format the background color of a cell based on text in that
cell.

If the cell contains "EXCELLENT", I want to color the background green
(works great).

If the cell contains "PARTIAL", "GOOD", or "MEDIUM", I want to color it
yellow. I've tried putting the following in a Cell, but it just returns a
#Value error.

=OR(SEARCH("MEDIUM", D4) 0, SEARCH("PARTIAL",D4)0,Search("GOOD", D4)0)

When I only put one of these critiera in the OR function, it returns the
proper logical value. Any ideas what I am doing wrong?

Actually, I tested =SEARCH("GOOD", D4) and =Search("PARTIAL", D4)
individually as the cell forumla and they each returned a #Value! result.

Any ideas how to make this work?

--
Email address is not valid.
Please reply to newsgroup only.

Conditional Formatting help required
 

=OR(ISNUMBER(SEARCH("partial",D4)),ISNUMBER(SEARCH (F2,"good")),ISNUMBER(SEARCH(F3,"medium")))

Ooops!

The cell references should all be D4.

Biff

"T. Valko" wrote in message
...
If the cell contains "PARTIAL", "GOOD", or "MEDIUM",

Are those words a *substring* or are they the only string?

If they're the only string:

=OR(D4="PARTIAL",D4="GOOD",D4="MEDIUM")

If those words are a *substring*:

=OR(ISNUMBER(SEARCH("partial",D4)),ISNUMBER(SEARCH (F2,"good")),ISNUMBER(SEARCH(F3,"medium")))

Biff

"Dale Fye" wrote in message
...
I am trying to format the background color of a cell based on text in that
cell.

If the cell contains "EXCELLENT", I want to color the background green
(works great).

If the cell contains "PARTIAL", "GOOD", or "MEDIUM", I want to color it
yellow. I've tried putting the following in a Cell, but it just returns
a
#Value error.

=OR(SEARCH("MEDIUM", D4) 0, SEARCH("PARTIAL",D4)0,Search("GOOD", D4)0)

When I only put one of these critiera in the OR function, it returns the
proper logical value. Any ideas what I am doing wrong?

Actually, I tested =SEARCH("GOOD", D4) and =Search("PARTIAL", D4)
individually as the cell forumla and they each returned a #Value! result.

Any ideas how to make this work?

--
Email address is not valid.
Please reply to newsgroup only.

Conditional Formatting help required
 

Thanks, Biff.

Substring (could be several differnet values). Isn't it a pitty we cannot
standardize functions across VBA so that INSTR( ) from Access would work in
Excel?

I'll give that a try.
--
Email address is not valid.
Please reply to newsgroup only.


"T. Valko" wrote:

If the cell contains "PARTIAL", "GOOD", or "MEDIUM",

Are those words a *substring* or are they the only string?

If they're the only string:

=OR(D4="PARTIAL",D4="GOOD",D4="MEDIUM")

If those words are a *substring*:

=OR(ISNUMBER(SEARCH("partial",D4)),ISNUMBER(SEARCH (F2,"good")),ISNUMBER(SEARCH(F3,"medium")))

Biff

"Dale Fye" wrote in message
...
I am trying to format the background color of a cell based on text in that
cell.

If the cell contains "EXCELLENT", I want to color the background green
(works great).

If the cell contains "PARTIAL", "GOOD", or "MEDIUM", I want to color it
yellow. I've tried putting the following in a Cell, but it just returns a
#Value error.

=OR(SEARCH("MEDIUM", D4) 0, SEARCH("PARTIAL",D4)0,Search("GOOD", D4)0)

When I only put one of these critiera in the OR function, it returns the
proper logical value. Any ideas what I am doing wrong?

Actually, I tested =SEARCH("GOOD", D4) and =Search("PARTIAL", D4)
individually as the cell forumla and they each returned a #Value! result.

Any ideas how to make this work?

--
Email address is not valid.
Please reply to newsgroup only.

Conditional Formatting help required
 

If you have a whole bunch of words to check it would probably be easier to
list those words in a range of cells then use this more compact formula:

G1:G10 = list of words

=OR(ISNUMBER(SEARCH(G1:G10,D4)))

Biff

"Dale Fye" wrote in message
...
Thanks, Biff.

Substring (could be several differnet values). Isn't it a pitty we cannot
standardize functions across VBA so that INSTR( ) from Access would work
in
Excel?

I'll give that a try.
--
Email address is not valid.
Please reply to newsgroup only.


"T. Valko" wrote:

If the cell contains "PARTIAL", "GOOD", or "MEDIUM",

Are those words a *substring* or are they the only string?

If they're the only string:

=OR(D4="PARTIAL",D4="GOOD",D4="MEDIUM")

If those words are a *substring*:

=OR(ISNUMBER(SEARCH("partial",D4)),ISNUMBER(SEARCH (F2,"good")),ISNUMBER(SEARCH(F3,"medium")))

Biff

"Dale Fye" wrote in message
...
I am trying to format the background color of a cell based on text in
that
cell.

If the cell contains "EXCELLENT", I want to color the background green
(works great).

If the cell contains "PARTIAL", "GOOD", or "MEDIUM", I want to color it
yellow. I've tried putting the following in a Cell, but it just
returns a
#Value error.

=OR(SEARCH("MEDIUM", D4) 0, SEARCH("PARTIAL",D4)0,Search("GOOD",
D4)0)

When I only put one of these critiera in the OR function, it returns
the
proper logical value. Any ideas what I am doing wrong?

Actually, I tested =SEARCH("GOOD", D4) and =Search("PARTIAL", D4)
individually as the cell forumla and they each returned a #Value!
result.

Any ideas how to make this work?

--
Email address is not valid.
Please reply to newsgroup only.

Conditional Formatting help required
 

You could define your own function to make use of INSTR in VBA -
however, a UDF is likely to take longer to evaluate a result than
using worksheet functions directly (depending on how complex they
are).

Pete

On Mar 30, 7:14 pm, Dale Fye wrote:
Thanks, Biff.

Substring (could be several differnet values). Isn't it a pitty we cannot
standardize functions across VBA so that INSTR( ) from Access would work in
Excel?

I'll give that a try.
--
Email address is not valid.
Please reply to newsgroup only.



"T. Valko" wrote:
If the cell contains "PARTIAL", "GOOD", or "MEDIUM",

Are those words a *substring* or are they the only string?

If they're the only string:

=OR(D4="PARTIAL",D4="GOOD",D4="MEDIUM")

If those words are a *substring*:

=OR(ISNUMBER(SEARCH("partial",D4)),ISNUMBER(SEARCH (F2,"good")),ISNUMBER(SEA*RCH(F3,"medium")))

Biff

"Dale Fye" wrote in message
...
I am trying to format the background color of a cell based on text in that
cell.

If the cell contains "EXCELLENT", I want to color the background green
(works great).

If the cell contains "PARTIAL", "GOOD", or "MEDIUM", I want to color it
yellow. I've tried putting the following in a Cell, but it just returns a
#Value error.

=OR(SEARCH("MEDIUM", D4) 0, SEARCH("PARTIAL",D4)0,Search("GOOD", D4)0)

When I only put one of these critiera in the OR function, it returns the
proper logical value. Any ideas what I am doing wrong?

Actually, I tested =SEARCH("GOOD", D4) and =Search("PARTIAL", D4)
individually as the cell forumla and they each returned a #Value! result.

Any ideas how to make this work?

--
Email address is not valid.
Please reply to newsgroup only.- Hide quoted text -

- Show quoted text -