In my training course the other day a student asked me how they could make Excel change the background of a cell when they typed in a new word. The simplest way I feel is using Conditional Formatting. A while back I wrote a tip on how to change the whole row of a spreadsheet with the use of conditional formatting. So, I pointed them to this. I shall however also examine this problem here today.
How to apply colour conditional formatting
You start off by applying conditional formatting to the area where you will be typing in the data you wish to have change colour according to what the data entails.
- Begin with selecting the area.
- Select Conditional formatting tool in the Home tab.
You are presented with a list of options. 
For this exercise I shall use the Highlight Cell Rules.
A further pop-out list appears. 
I shall use the Text that contains, as the student required it to be text driven.
This dialog box appears. 
Have your possible list of text handy so that you know what word to use for the first to the last setting. Consider using strong colours for the background.
I have set just a few key words with colour for this area. My list is “World”, “Europe”, “USA” and “Australia”.
This is what the Text the contains dialog box looks like for the first setting. 
Where the custom format is a fill colour of dark green.
After repeating this process for all possible entries, you can either test the area using just the one word of a series of words with the one in the setting being part of the string. Or you can visit the full list of settings.
- Select Conditional Formatting Tool.
- Select Manage Rules at the bottom of the list.
This is what the dialog box would look like. 
Here to you can add another rule if needs be.
Having set these setting up before you enter data it is quite fun to see the text change as you start to input new data in this area. 
If the data changes so will the formatting.
