Conditional Formatting

[watermt]

I'm attempting to do some conditional formatting on text. I need to
highlight cells that contain the word missing and also any cells that were
left blank.

is this possible?

 

[Kevin B]

Select the cells you want to assign conditional formatting to and click
FORMAT in the menu and select CONDITIONAL FORMATTING:

Drop down the options in the CONDITION 1 combo box and select FORMULA IS

In the text box to the right enter the following formula, replacing A1 with
the currently active cell in your selected range:

=OR(A1="",A1="missing")

Click the format button and then select the formats you want to apply and
click OK twice to exit back to the worksheet

 

[Pete_UK]

Highlight all the cells you want this to apply to (assume the first of
these is A1), then click on Format | Conditional Formatting and in the
dialogue box choose Formula Is rather than Cell Value Is in the first
box. Enter this formula in the next box:

=OR(A1="",ISNUMBER(SEARCH("missing",A1)))

Click on the format button and the Patterns Tab to choose your
highlight colour. Click OK (twice) to apply these settings - Excel
will automatically adjust the cell references to suit your selected
cells, but if your first cell was not A1 then you will have to make
the appropriate changes to the formula I gave you.

Hope this helps.

Pete

 

[RagDyeR]

In Conditional Formatting, select "Formula Is", and try this:

=OR(A1="",COUNTIF(A1,"*missing*")>0)

Assumes "missing" may be combined with other text in the same cell.
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

I'm attempting to do some conditional formatting on text. I need to
highlight cells that contain the word missing and also any cells that were
left blank.

is this possible?

 

[watermt]

I tried your suggestion and my conditional formatting ended up being applied
to every cell in the range. Thanks for the reply though!

 

[watermt]

Kevin,
I went back and double checked your suggestion and realized that I had not
"cleared" my previous attempt at applying conditional formatting, so this is
why your suggestion dsiplayed all cells formatted. I corrected this and
still had a problem, only the cells labeld missing were formatted and your
formula overlooked the blank cells.

Mike

 

[watermt]

Pete,
Thanks your suggestion worked, much appreciated!

Mike

 

[watermt]

RD,
Your formula and Pete's both worked as I had wished, thanks!

 

[Pete_UK]

You're welcome, Mike - thanks for feeding back.

Pete

 

[watermt]

Is there a limit to how many arguments I can use with your formula?

 

[Pete_UK]

I think OR can take up to 31 arguments.

Pete

 

[RagDyeR]

Appreciate the feed-back.

 

[watermt]

Thanks again Pete!