Conditional format cells based on match in another sheet

Question

I have 2 excel sheets (Using Microsoft excel 2011 for mac, but tried the same on excel 2010 as well)

sheet1(Column D)
foo
bar

sheet2
abc cde foo
sfd sdf dgf
bar

I have applied 3 different rules using conditional format:
1. If any of the data in Sheet2 matches with Sheet1(ColumnD) - Make text as blue
2. If there are any duplicate values in sheet2 - Make text as yellow
3. If the above 2 are not there - leave it blank
4. If both Rule 1 and Rule 22 are met - Make text as green

What can be Rule 4 ?

Rules:

 Rule 1 # =ISERROR(MATCH(A1,Sheet1!$D:$D,A1,0))=FALSE
 Rule 2 # Highlight Duplicate values with yellow text
 Rule 3 # =ISBLANK($1:$1048576)
 Rule 4 # (Not sure what to have as formula)

Answer 1

I don't understand your question but suspect you only need two rules anyway:

1. a formula rule =NOT(ISERROR(MATCH(A1,Sheet1!$D:$D,0))) with blue font and
2. format only unique or duplicate values with duplicate selected, with yellow font.

Conditional Formatting will not allow a rule that returns both yellow and blue font.

---

A formula rule for say green font where both the above conditions apply, assuming the range to apply to is A:C :

=AND(COUNTIF($A:$C,A1),NOT(ISERROR(MATCH(A1,Sheet1!$D:$D,0))))

In some cases whether green or yellow or blue may depend upon the order the rules are applied and whether "Stop If True" is checked.

Answer 2

Use COUNTIF, in the same way excel uses count for the duplicates.

Using relative addressing to 'cheat' the system, highlight from A1 to where you want the formatting to stop, then we can use the relative addressing to check each cell

Rule 1: =(CountIf(Sheet1!$D:$D,A1)>0)
Rule 2: =(CountIf(Sheet2!$A:$XFD,A1)>1) (This is what excel does when you tell it to highlight duplicates)
Rule 3: not needed, as if it doesn't match a rule, then no formatting will be applied
Rule 4: =(AND((CountIf(Sheet1!$D:$D,A1)>0),(CountIf(Sheet2!$A:$XFD,A1)>1)))

If you start the highlighting from any other cell, change the A1 in the rules to whatever cell you start the formatting on.

I would also recommend keeping away from using the full sheet, as it is going to get horrible performance.