Conditional Formatting for Formula Cells

[Vibeke]

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.

 

[T. Valko]

Why do you have this at the end of your formula: &""

Conditional Formatting
Formula Is: =B1<>""

Biff

 

[Max]

Try this ..

Assume your range is A110

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

 

[T. Valko]

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]

They have any errors trapped in the formula ..

True that's what the OP posted, but we can treat the suggestion as generic
then <g>, just in case the formulas range does have any untrapped error
returns.

 

[Vibeke]

You asked..."Why do you have this at the end of your formula: &""

Because the data populated by the function is a combination of two bits of
data from the other speadsheet (the name of the event and the date it occurs).

Thanks you for your replies, I will now give them a try.

 

[Vibeke]

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]

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 sheet 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

 

[T. Valko]

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 sheet 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

 

[Vibeke]

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

 

[T. Valko]

You're welcome. Thanks for the feedback!

Biff

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