Conditional formating based on text

M

mango7

I want to make all cells which contain a question mark to have a red
fill but the conditional formating options don't allow you to make an
"if a cell contains ? then" format.
 
S

SteveG

Starting in the first cell of your range (in this case A5),
Format>Conditional formatting. Change the CF option from Cell Value is
to Formula is.

=FIND("?",A5,1)>0

Select Format, Patterns, Red. Click OK, and OK again. Use the format
painter to apply to all cells in your range.

Does that help?

Steve
 
B

Bob Tarburton

for cell A1 in conditional formatting enter the following function
=ISNA(FIND("?",A1,1))=FALSE
probably a simpler formula on the way too.
 
B

Bob Tarburton

Oops!
ISERROR instead of ISNA

Bob Tarburton said:
for cell A1 in conditional formatting enter the following function
=ISNA(FIND("?",A1,1))=FALSE
probably a simpler formula on the way too.
Click to expand...
 
M

mango7

Hi,
That has worked great thanks. I was trying to create a formula to do i
myself but couldn't work out the intricacies. You couldn't explain th
formula to me could you so I know what each bit is and can edit it an
learn to make my own.

=FIND("?",A5,1)>0

I get the =find bit ok, and the open brackets defines what to be found
By putting the ? in "" does that mean that any other text in the cell i
ignored and doesn't matter? Or are they just necessary whatever th
value being searched for is? I understand the next thing is where t
search, the current cell number. It's the rest I don't understand. Wh
is the a comma and then 1? and why outside the brackets do you nee
0?
Click to expand...

Sorry about this. Thank yo
 
M

mango7

Hi,
Someone else also replied and I did that but I tried yours as well and
it worked great thanks. I was trying to create a formula to do it
myself but couldn't work out the intricacies. You couldn't explain the
formula to me could you so I know what each bit is and can edit it and
learn to make my own could you?

=ISERROR(FIND("?",A1,1))=FALSE

I get the find bit ok, and the open brackets defines what to be found.
By putting the ? in "" does that mean that any other text in the cell
is ignored and doesn't matter? Or are they just necessary whatever the
value being searched for is? I understand the next thing is where to
search, the current cell number. It's the rest I don't understand. Why
is the a comma and then 1? and why outside the brackets do you need
=false?

Sorry about this. Thank you
 
S

SteveG

Well you could actually use

=FIND("?",C7)

The text needs to be in " " to define it as text.

The other info was me including all the parameters of the FIND function
and then thinking that the CF would need to analyze the return of the
function, which by trial and error I discovered it did not as the above
proves.

This is what the FIND does.

=FIND(text,withintext,starting number)

so =FIND("?",A5,1) = Find the text ? within the text in cell A5
starting with the first character in the text string. If it is not
found then it would return an error.


Steve
 
Top