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:
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 not present 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 is not present. 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 name is present.
Although the formula I gave you works, here is a simpler one
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
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.