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.

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 trueThe word

TRUEis 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?

Yep! but I will give it a try. Thanks

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.

??

Hi, 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 trueHere is an example

=IF($A$1="My word", TRUE,FALSE)

Click the Format... button and select a Fill colorNow 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 isnotcase 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.

Regards

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...theyou should choose the formatting option that allows you to Format thetextin range B1.M1 to be redFontColor, not theFillColor.

Eureka!! Works exactly as I need. That information allows me to learn and adapt the procedure for a variety uses. Many thanks.

David.

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.

David

Hi, 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 OKRegards

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.

Hi, If you wanted cells C1 to C200 to change color if

anycell 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)=IF(ISNA(VLOOKUP("SHOP",$C$1:$C$200,1,FALSE)),FALSE,TRUE)

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)Regards

Appreciated. Can't try it until I get into work tonight (19:00 GMT).

Many thanks for your help.

Hi, 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"

and

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.

Regards

Ask Your Question

Weekly Poll

Did you buy anything on Amazon's Prime Day?

Discuss in The Lounge

Poll History