conditional format for text

[Kim]

Hi
Can some one help to setup a conditional format, the condition is turn the
text red when ever the cell contains an "X". I can see the examples given
for numbers but not text and I tried substitute for text but still no joy an
dended up with lots of duff conditions in the examples.
Please help.

Cheers

Kim

 

[Govind]

Hi,

Lets say the text is in A1,

Go to conditional formatting,Choose Formula is and type in

=IF(ISTEXT(A1),TRUE,FALSE)

and then choose your format.

Regards

Govind.

 

[Max]

Try these 3 ways ..
(one of which should hopefully fit what you're after)

Select col A
Click Formatting > Conditional Formatting

(A)
Condition 1:
Formula is: =TRIM(A1)="x"
Format > Font tab > Red/bold
OK out

Above will format any cells in col A
which contain *only* an "x"
(not case-sensitive)

or perhaps ..

(B)
Condition 1:
Formula is: =NOT(ISERROR(FIND("X",A1)))
Format > Font tab > Red/bold
OK out

Above will format any cells in col A
which contain any text with at least one capital "X"
(Case-sensitive)

(C)
Condition 1:
Formula is: =NOT(ISERROR(SEARCH("X",A1)))
Format > Font tab > Red/bold
OK out

Above will format any cells in col A
which contain any text with at least one "x"
(non case-sensitive)

 

[Kim]

Govind,
Thanks for your quick reply, I tried typing in something like this in the
(formats, cells, custom) "=if(istext(range),true,[red])" but excel tells me
it can't use the formula, am I missing your drift?
say I want the text colour to be red whenever a cell contains text instead
of number, can you help me more?

Cheers

Kim

 

[Max]

say I want the text colour to be red whenever a cell

contains text instead of number ..

Try ..

Select col A
Click Format > Conditional Formatting

Condition 1:
Formula is: =ISTEXT(A1)
Format > Font tab > Red/bold
OK out

Btw, I've given you some suggestions to your original post
in the other thread. How did these go with you ?

--
Rgds
Max
xl 97
--
Please respond, in newsgroup
xdemechanik <at>yahoo<dot>com
---

Govind,
Thanks for your quick reply, I tried typing in something like this in the
(formats, cells, custom) "=if(istext(range),true,[red])" but excel tells me
it can't use the formula, am I missing your drift?
say I want the text colour to be red whenever a cell contains text instead
of number, can you help me more?

Cheers

Kim

Hi,

Lets say the text is in A1,

Go to conditional formatting,Choose Formula is and type in

=IF(ISTEXT(A1),TRUE,FALSE)

and then choose your format.

Regards

Govind.

 

[Max]

Typo, sorry

Line

Click Formatting > Conditional Formatting

should read as ..

 

[Kim]

Thanks,

It turned red alright, but how do I apply to a range of cells? Please advice.

Kim

 

[Max]

If applying the CF to say B1:G20 instead of to col A as posted earlier,
just select the target range B1:G20, and change the CF formula
to point to the *top left corner* of the range, i.e. to B1instead of A1

Example:
(C)
Condition 1:
Formula is: =NOT(ISERROR(FIND("X",B1)))
Format > Font tab > Red/bold
OK out

The CF formula will adjust itself accordingly within each cell in the range
selected

Alternatively, you can use the format painter to apply
Select any one cell within the conditionally formatted col A, say A3 ?
Double-click on the format painter, then "paint" over the target range
B1:G20
with the cursor as a brush

Press Esc when done to cancel and revert the cursor to normal