Re: Conditional Formatting for Formula Cells

Your solution worked. Thank you very much! I appreciate all of your efforts
to assist.

"T. Valko" wrote:

Ooops! I forgot that you were looking for a substring:

If you have a whole bunch of these then it will probably be better to list
them somewhere on your *** and then use something like this:

F1 = ABC
F2 = HIJ
F3 = QRS
F4 = DEF
F5 = KLM
F6 = TUV

Then:

Condition 1
Formula Is: =MATCH(B1,F1:F3,0)

Condition 2
Formula Is: =MATCH(B1,F4:F6,0)


Make those formulas:

Condition 1
Formula Is: =OR(ISNUMBER(SEARCH(F1:F3,B1)))

Condition 2
Formula Is: =OR(ISNUMBER(SEARCH(F4:F6,B1)))

Biff

"T. Valko" <biffinpitt@xxxxxxxxxxx> wrote in message
news:e04gM5lZHHA.4872@xxxxxxxxxxxxxxxxxxxxxxx
Try this:

Condition 1
Formula Is:

=OR(ISNUMBER(SEARCH("ABC",B1)),ISNUMBER(SEARCH("HIJ",B1)),ISNUMBER(SEARCH("QRS",B1)))

Condition 2
Formula Is:

=OR(ISNUMBER(SEARCH("DEF",B1)),ISNUMBER(SEARCH("KLM",B1)),ISNUMBER(SEARCH("TUV",B1)))

If you have a whole bunch of these then it will probably be better to list
them somewhere on your *** and then use something like this:

F1 = ABC
F2 = HIJ
F3 = QRS
F4 = DEF
F5 = KLM
F6 = TUV

Then:

Condition 1
Formula Is: =MATCH(B1,F1:F3,0)

Condition 2
Formula Is: =MATCH(B1,F4:F6,0)

Biff

"Vibeke" <Vibeke@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:103FD7CB-0CD1-43AD-AFDB-B9EAFC19E3B4@xxxxxxxxxxxxxxxx
Thank you very much for your assistance. Worked a charm. If I could
trouble
you with an extended question on a similar problem, I would be very
grateful
for any assistance.

Column B's formula returns an event and a date. Depending on the task,
and
the type of event, a different staff member is assigned to complete the
task.
I would like to create a conditional format for each task which will do a
wildcard search on the text in B1.

So condition 1 might be: find any text in B1 containing "*ABC*", "*HIJ*"
or
"*QRS*", then pink.
Condition 2 would be : find any text in B1 containing "*DEF*", "*KLM*",
or
"*TUV*", then yellow

Fortunately, condition 3 would be the formula you provided in my earlier
query!

I've searched the discussion groups (and learned A LOT - now wouldn't
that
be a great function?), but despite trying variations of SEARCH, AND, IF
and
OR, I'm no further a-head for all my head scratching (So to speak).

Many thanks & curtsies.




"T. Valko" wrote:

Condition 1, Formula is: =ISERROR(A1)
Format: White font (to mask the error returns, if any)

They have any errors trapped in the formula:

=IF(ISERROR(VLOOKUP(A3,'calendar~data'!$B$5:$O$36,14,0)),"",........

Biff

"Max" <demechanik@xxxxxxxxx> wrote in message
news:C2D27D78-FEE5-4666-8EEF-53B00996117F@xxxxxxxxxxxxxxxx
Try this ..

Assume your range is A1:D10

Select the range (with A1 active),
then apply CF using settings:

Condition 1, Formula is: =ISERROR(A1)
Format: White font (to mask the error returns, if any)

Condition 2, Formula is:
=AND(A1<>"",NOT(ISERROR(A1)))
Format: Blue fill

OK out
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Vibeke" wrote:
Hi
I have a workbook that creates a monthly task calendar in one of the
spreadsheets. in that sheet, there is a list of tasks in Column A,
while
Column B contains the formula :
=IF(ISERROR(VLOOKUP(A3,'calendar~data'!$B$5:$O$36,14,0)),"",VLOOKUP(A3,'calendar~data'!$B$5:$O$36,14,0))&""

where A3 is a date. The cells in Column B therefore appear either
blank
or
return a value.

I want to apply conditional formatting only to those cells in Column
B
that
return a value. The colour formatting will alert a staff member that
they
have a task to complete on a certain day if it is shaded with 'their'
colour.
I've fuddled around with variations including IF, ISBLANK and
NOT(ISBLANK)
but to no avail. can anyone assist?

Thanks.








.

Loading