Excel 2007 : Conditional formatting based on text in another cell

**ohjeez** · 11-23-2010, 10:37 PM

Hi all,

I have a list of data like this (eg A3 to A8)
Beach Cove Field Engineer
Beach Cove Transport
Beach Cove Admin
Greenfield Field Engineer
Greenfield Transport
Greenfield Admin

I then have a cell (A1) which has the following location: 'Greenfield'

What I would like to do is conditionally format all of the data based on the word in A1 (so that this can be changed easily). So say A1=Greenfield, I would like any cell which does NOT contain 'Greenfield" to be coloured red.

The reason for having cell A1 is so that the location can be easily changed, without editing the conditional formatting rule.

I might be missing something, but I can't seem to make this happen. Any ideas would be much appreciated!

**mewingkitty** · 11-23-2010, 10:57 PM

Opposed to the idea of a helper column?
See attached file.
What I've done is created a column which seperates the left most word of the words in column A.
After that the rest is done by conditional formatting... with a bit of creativity.
Check out the font and the background color formatting, and how that corresponds to the conditional formatting, there's also a change to the alignment format to account for the words spilling over to the next cells.

Let me know if this helps you any, or gives you any ideas.

There's probably a way to do this with a formula as the conditional formatting command, but I don't know what it is, so this is my best guess

mew!