Hiya, I'm trying to colour a cell when a different cell meets a certain criteria eg. a girl is in the school house called Aidan which is yellow her name and her house is on one sheet in my spreadsheet and is coloured yellow, her name is then duplicated on another sheet in my spreadsheet and I want it to go yellow again but I do not want to hand do this I want it to change automatically. I have already tried conditional formatting but that hasnt worked. Any ideas? PLEASE!!! :D

thanks,

Hi, Here is an example of conditional formatting that you should be able to use.

There are two lists of names, one for each of two school houses:

G H 3 A house B house 4 Jane MaryLou 5 Fay Hannah 6 Zoe June

Cell B4 contains a name and has two conditional formats set, both using formulas:

1st condition:=IF(NOT(ISERROR(VLOOKUP(B4,G4:G6,1,FALSE))),TRUE,FALSE)Conditional Format set to yellow background

2nd condition:=IF(NOT(ISERROR(VLOOKUP(B4,H4:H6,1,FALSE))),TRUE,FALSE)Conditional Format set to green background

If a name in the A house list is entered in cell B4, the cell background is yellow,

If a name in the B house list is entered in cell B4, the cell background is green and

If any other name is entered in cell B4, the cell has no background colour.Regards

Hiya, Thanks for the reply,

Just a few questions:

How do the formulas know which colour to colour the cell

Also how do you enter the person's name as well as the two formulas?

And what does ISERROR mean?Thank you! :)

Hi, From your post, I thought that you were familiar with conditional formating formulas, which is why I didn't provide any instructions for using it, so here is some information and instructions for Excel 2007.

Conditional formating allows certain features of a cell, such as background colour to change based on the value of the cell itself or the value of another cell. In Excel 2007, MS has added a lot of pre-built settings, but you can also create your own criteria using formulas.

These formulas ar not entered into the cell directly, you can think of them as attached to the cell and controlling its appearance. These formulas are entered by using conditional formating dialog boxes.

In Excel 2007, select cell B4, then from the Ribbon select Home, then Styles and Conditional formating (CF).

From the CF drop-down select Manage Rules, and click the new rule button, and select 'Use a formula to determine which cells to format'.In the box underneath this, there is a box labeled Format values where this formula is true.

Enter the first of the two formulas:=IF(NOT(ISERROR(VLOOKUP(B4,G4:G6,1,FALSE))),TRUE,FALSE)

Now click the format button, and this is where you select what the cell will look like if the first formula is TRUE, in this case if the name in the cell is in the list under A house. Select the Fill Tab and a yellow colour. Click OK twice and you will see the formula under Rule Applied, followed by an example of the format and the cell or cells this CF applies to.Now add the second CF - click New Rule and follow the same steps, but this time use the second formula:

=IF(NOT(ISERROR(VLOOKUP(B4,H4:H6,1,FALSE))),TRUE,FALSE)and in the Format - Fill section select a green colour. Click OK twice, and you will now see the two formulas and the two formats, and both will apply to cell B4. Click Apply and OK.You can now enter a name in Cell B4, and the cell will be yellow if the name is in A house, green if in B house or the default background if in neither.

Regarding ISERROR. The function I used to find if the name in cell B4 was present in either of the two lists was VLOOKUP. If the name is not present in the list, the VLOOKUP function returns an error message. One way to know if the name is

notpresent is to check for the error, and this is what ISERROR does. ISERROR returns the logical value TRUE if VLOOKUP returns an error, i.e., the name isnotpresent. I used NOT to reverse the result, so now, if the name is not present the formula is FALSE, and conversely it is TRUE when the nameispresent.Although the formula I gave you works, here is a simpler one

=IF(COUNTIF(G4:G6,B4)>0,TRUE,FALSE)

This uses COUNTIF to test if the name (in cell B4) is present in the relevant list. If present it will return 1 if not present it will return 0. The IF function does a logical test to see if COUNTIF returns a value >0. If COUNTIF is >0 then the name is present and the result is TRUE.

The second formula is=IF(COUNTIF(H4:H6,B4)>0,TRUE,FALSE)You can add further CFs to the cell to test more lists. In Excel 2003 the limit was 3 CFs, but it is almost unlimited in Excel 2007.

Regards

Ask Your Question

Weekly Poll

Would you ride in a self-driving car from Tesla?

Discuss in The Lounge

Poll History