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!