Formating a range with cell entry

Excel Excel 2007 home and student
February 13, 2010 at 13:31:17
Specs: Windows 7
Good evening, I have been trying to achieve the following in Excel 2007 but can't quite get there.
I want to enter a word into A1 that if true turns text in range (B1 to H1) a specific colour.

Many thanks.

See More: Formating a range with cell entry

February 13, 2010 at 14:11:44
Look at Conditional Formatting.

I don't have 2007, so I can't guide you through the menus, but I can offer the formula to use.

First - a caution:

You said "...enter a word into A1 that if true"

The word TRUE is a reserved word in Excel and can cause problems when used in a formula.

For example, if you use this as a Conditional Formatting )CF) formula and enter Dog in A1, the CF will occur:

=IF(A1 = "Dog",TRUE,FALSE)

However, if you use this and enter True in A1, the CF won't ocurr:

=IF(A1 = "True",TRUE,FALSE)

This is because when you enter True, Excel assumes you mean the reserved word TRUE and not the text string True.

To make the CF work for True, you would have to enter a single quote before the word to tell Excel that the entry is Text. i.e. 'True

Finally, if you use =IF(A1 = TRUE,TRUE,FALSE), then entering the text string True would not be TRUE and the CF wouldn't work. I did not put quotes are the first TRUE, so Excel is going to assume I mean the reserved word TRUE.

For example, if I entered =B1=B1 in A1, then the result would be TRUE and the CF would occur.

Are you now TRUEly confused?

Report •

February 13, 2010 at 14:25:19
Yep! but I will give it a try. Thanks

Report •

February 13, 2010 at 14:55:44
Well I tried that and managed to get a cell and a range of cells to CF but I can't tell A1 (Say) to carry out CF in range B1.M1 (Say).

I want to enter "Paramount" in A1 and the text in range B1.M1 to be red. But if not "Paramount" then text be default.


Report •

Related Solutions

February 13, 2010 at 15:27:01

DerbyDad03's suggestion of using Conditional formatting will do what you want.

Here are the steps for Excel 2007:
Select cells B1 to H1
From the Ribbon select Home - Styles - Conditional Formatting
From the drop down select New Rule and the last option - 'Use a formula to determine which cells to format'
In the 'Format values where this formula is true' box, enter a formula that evaluates to true

Here is an example
=IF($A$1="My word", TRUE,FALSE)
Click the Format... button and select a Fill color

Now if cell A1 contains "My word", all the cells in the range B1 to H1 will have your selected background color.
(For some reason CF is not case sensitive so the color will change for "MY WORD" as well).

Note the $ signs in the cell address - they are important - just use A1 and only cell B1 will respond appropriately.


Report •

February 13, 2010 at 15:44:40
Follow Humar's step by step instructions, except for this part:

"Click the Format... button and select a Fill color"

Since you said I want...the text in range B1.M1 to be red you should choose the formatting option that allows you to Format the Font Color, not the Fill Color.

Report •

February 14, 2010 at 03:03:09
Eureka!! Works exactly as I need. That information allows me to learn and adapt the procedure for a variety uses. Many thanks.

Report •

February 15, 2010 at 07:31:40
The advice above was very helpful and I have done what I needed to do, and it works fine however I use Excel2007 at home so number of Cf's not a great problem. But Excel at work limited to 3 CF's. Any advice on how to get a column to apply a cell fill colour if the word "SHOP" is entered in a cell in that column?
Many thanks.

Report •

February 15, 2010 at 08:15:27

The limit of 3 Conditional formats prior to Excel 2007 refers to the number of possible formats per cell, not the number of cells with the same conditional format. In your example for Excel 2003, you are only applying one CF.

Three CF's might be cell red if value less than 10, yellow if cell 11-20 and green if over 20.

If cell C1 contains SHOP or something else
To CF cells C2 to say C200, select cells C2 to C200
From the Menu - Format - Conditional Formatting...
In the dialog box select 'Formula Is' in the 'Condition 1' drop-down
In the formula box enter =IF($C$1="SHOP",TRUE,FALSE)
Click Format button and select your formatting (use Patterns for background fill)
Click OK


Report •

February 15, 2010 at 08:27:23
Hi Humar. Perhaps I have applied my previous CF's incorrectly because when I tried to apply the 4th CF for the SHOP fill it would not action. On the previous 3xCF's for the font colour changes I created a CF for each action (I created a CF, then checked it worked then added a second CF etc. All CF's over range A to M with trigger ($M1) being entry into column M) The drop down CF box shows 3x CF created. Should I have created all 3 CF in one CF box? I need entry into column M to action the SHOP fill as well over the same range as the others.

Report •

February 15, 2010 at 08:34:07

If you wanted cells C1 to C200 to change color if any cell in the range C1 to C200 contained the word shop, put this in the conditional format formula box
(Select cells C1 to C200, from the Menu bar select Format - Conditional formatting... and Condition1 = Formula Is)


Copy the formula and use Ctrl+V to paste it into the formula box
(Thanks to DerbyDad03 for the Ctrl+C copy Ctrl+V paste in Excel boxes which don't have a copy or paste context menu)


Report •

February 15, 2010 at 08:41:46
Appreciated. Can't try it until I get into work tonight (19:00 GMT).
Many thanks for your help.

Report •

February 15, 2010 at 08:58:20

I got a bit lost with your response #9

If there is one 'trigger', in your case cell M1 is either SHOP or not SHOP, then you should only need one level of CF.

If any cell in column M is SHOP or not SHOP this is still one 'trigger'.

Both examples can evaluate to TRUE or FALSE
TRUE if cell M1="SHOP"
or TRUE if any cell in column M ="SHOP"
FALSE if cell M1<>"SHOP"
or FALSE if no cell in column M ="SHOP"

Try creating the CF in one cell, then drag the cell and the CF formula is extended with it, just like a normal cell formula.


Report •

Ask Question