Menu
Ask a question Report

How to use conditional format based on text? [Solved/Closed]

Posts
1
Registration date
Friday November 9, 2012
Last seen
November 9, 2012
 -
Excel Ask a question Report
Hello,

I want a cell to change color if another cell contains the words "first aid". The text comes down from a drop down list and could read "First Aid" or "sleepover/first aid".

If the cell has "first aid" in it, I want the other cell to change color. I can get it to work if using formula f13=first aid but it won't change color if the cell says sleepover/first aid?

Can anyone help?
System Configuration: Mac OS X Lion (10.7) / Firefox 16.0
See more 

7 replies

Best answer
approved by Ratnendra Ashok on Jan 13, 2019
Report Respond to Zohaib R
Posts
2403
Registration date
Sunday September 23, 2012
Last seen
December 13, 2018
 650
80
Thank you
Hi Leanne99,

You are following the right steps, instead of "f13=first aid" enters the below mentioned formula: 

=ISNUMBER(SEARCH("first aid",F13))=TRUE


For step by step help on this, in your reply, please mention the version of Microsoft Excel you are using (such as Excel 2003, 2007, 2010, etc.)

Please do write back to us.

Thanks & Regards
Zohaib R
#iworkfordell

Say "Thank you" 80

A few words of thanks would be greatly appreciated. Add comment

CCM 4458 users have said thank you to us this month

View all 15 comments
Report
-
its 2016 and this answer is still good! I used it to format cells that contained the word "Medium" but the look up was "Medium-8" through to "Medium-18" worked perfectly. Cheers Zohaib R :)
Report
-
Thanks for this post, saved me a lot of time.
Report
-
June 2017 - Thanks so much Zohaib, I was running in circles and wouldn't have thought of this (self taught, and I still don't understand its logic, but it works so I'm ecstatic!) ~K
Report
-
Thank you! Finally, a solution that worked for me! I needed to highlight every cell that contained 'yes' and this did it.
Report
-
Thanks
Report Respond to Harry
34
Thank you
Hi, I have 2 columns. I want to chenge the color of column B based on value in column A. If value in A is greater than or equal to zero, color should be green else red(I am doing this with conditional formatting and it works fine.)

Problem:
when the color is changing I want to add a text On track and color green or else delayed and color red. Is that possible?
Report Respond to Zohaib R
Posts
2403
Registration date
Sunday September 23, 2012
Last seen
December 13, 2018
 650
13
Thank you
Hi Leanne99,

Use the below mentioned steps to apply conditional formatting to the cells.

1. Select the cells where you want to apply the Conditional Formatting.
2. Click Conditional Formatting from the top ribbon.
3. Select New Rule.
4. Click Use a formula to determine which cells to format.
5. Enter the below mentioned formula in Format values where this formula is true:

=ISNUMBER(SEARCH("first aid",F13))=TRUE

6. You will all the cells which have the word "first aid" in it, the cells against them will be highlighted.

Please revert for clarification.
Report
-
I used the formula but it gives me different result, I mean not correct
Report
deeptis
Posts
6
Registration date
Thursday June 19, 2014
Last seen
July 25, 2014
 1 -
Hi Zohaib,

This is very helpful, is there a way to make the whole row against the cell with "first aid" to be highlighted, rather than just the cell next to it?
Report
advntr
Posts
1
Registration date
Monday August 4, 2014
Last seen
August 4, 2014
 -
This worked perfectly! Thanks Zohaib!
Report
-
Thank you very much,
Works perfectly.
Oscar N
Report Respond to leanne99
3
Thank you
Hi Zohaib

thank you so much for your reply. I am using Office 2011 for Mac. I have tried using and OR formula and this appears to be working although a little complex.

Again thank you for your reply

regards

Leanne99
Report Respond to Grinty
Posts
1
Registration date
Thursday February 28, 2013
Last seen
February 28, 2013
3
Thank you
This is particularly helpful, but can it be extended to respond in other colours where the answer in F13 may not be "first aid", but something else like "health & safety"?

So an either/or function of some sort?
Report Respond to Zohaib R
Posts
2403
Registration date
Sunday September 23, 2012
Last seen
December 13, 2018
 650
2
Thank you
Hi Grinty,

Please check the below mentioned link:

http://ccm.net/forum/affich-689619-conditional-formatting-change-fontcolor-based-on-another-cell

I have used AND Logical Operator to select three different criterion. Check if this helps.

If you need further assistance please post some more details about your datasheet. In addition, you can upload a sample file to: http://www.speedyshare.com/

Note: Before you upload any files please make sure there is no personal or confidential information contained in it.
Report Respond to landers
0
Thank you
I have two columns with different information. I want to be able to highlight certain information in both columns based off of the information entered. If Column E contains 38, 48, 55, 56, 58, 70 OR 713 and Colum C contains "service", then highlight both cells. I am on Office 2010.
Ask a question