Use formulas to select certain cells within a column?

[KaruiKage]

I have a rather large spreadsheet with a number of things laid out on it. This is over 300 lines and it gets assigned to a few different individuals. To make things easier, I was wondering if there was a way to do the following:

1. Have a cell near the top that I can type any word into, or maybe select a set of words from a drop-down list (this part I can do)
2. Once that word is typed/selected, have it take a specific column and highlight all cells within the column that contain said word. (this part I know not how to do).
3. If the word is changed, have it de-highlight the original cells and highlight the new ones within that column.
4. If the cell near the top is ever empty, have it de-highlight anything that was highlighted by this formula.

Any help is appreciated, thanks!

[darkyam]

#2-4 can be solved by one conditional formatting formula. Say the dropdown cell is A1. Highlight the column that you want this to apply to, then go to conditional formatting and use the rule: =B1=$A$1 (change B1 to the top cell in whatever range) and then format as desired.

[KaruiKage]

That seems to work beautifully on Excel 2007, but I can't get it to work on Excel 2003.

On 2007 I have it set as:
Formula: =C19=$I$6

Where C19 is the top of the column I want and I6 is where the dropdown menu is. The formatting works beautifully. If I try the same thing with 2003, however, it doesn't seem to work. Any ideas?

[darkyam]

"doesn't seem to work" is a bit vague. I don't see why it wouldn't, though, since the formula appears to be in order. The only other thing I might suggest is inputting the formula with just the first cell highlighted, then copying the cell and pasting special -> formatting for the rest of the selection.

If it still doesn't work, could you please post the workbook? Then perhaps someone with 2003 (or who remembers it well, unlike me) might be able to help.

[KaruiKage]

That did it, thanks!