Edit Cell Colour if other cell displays specific text

**macky18** · 11-08-2013, 07:30 AM

Hi there.

I have created a timetable at work. I want employees to be able to click on there name from a data validation list and there name on the timetable highlight green, so it is easy to identify what extra responsibilities they have at what time.

My drop down box is in cell O32.

In conditional formatting I am using the formula =ISNUMBER(SEARCH("helen",O32))=TRUE and changing the name in each box as appropriate.

As the timetable changes week by week what I would like in an ideal world is to have a drop down box of 20 or so names and depending on which name I select the conditional formatting to search the document for that name and highlight the cell green.

This way I don't have to manually change the formulas each week.

So I select the name "Paul" from the list in cell O32 and it searches the sheet for "Paul" and highlights any cell with that text.

Hope that makes sense.

Neil.

**JBeaucaire** · 11-08-2013, 07:45 AM

I would think the CF formula would be something like this:

1) If the range of cells with people's names were A1:M100, highlight those cells
2) Apply this CF formula:

=A1=$O$32

If you can't adapt that to work in your actual workbook, attach it here.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

**Olly** · 11-08-2013, 07:46 AM

Change your conditional formatting formula to:

Formula:

=ISNUMBER(SEARCH($O$32,B2))=TRUE

For example, the above formula works for cell B2 - leave this reference relative, and copy the conditional formatting to the full range where you need it.

Edit: see attachment for an example.