Chameleon : image credit - unsplash.com/erwan_hesryIn 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.selected area
  • Select Conditional formatting tool in the Home tab.Conditional formatting tool

You are presented with a list of options. Conditional Formatting Selections

For this exercise I shall use the Highlight Cell Rules.

A further pop-out list appears. Highlighted Cell Rules

I shall use the Text that contains, as the student required it to be text driven.

This dialog box appears. Conditional Formatting Dialog BoxHave 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. Text that contains boxWhere 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. Rules ManagerHere 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. Result 1If the data changes so will the formatting.

Tips – Formatting explained in Excel.

Formatting in cells of Excel – 3 of 8

 

LEAVE A REPLY

Please enter your comment!
Please enter your name here