0

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)
Jill448
  • 1,745
  • 10
  • 37
  • 62

2 Answers2

1

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.

SeanC
  • 15,695
  • 5
  • 45
  • 66
  • @pnuts, changed to complete sheet. – SeanC Dec 12 '14 at 22:16
  • When I am trying to add Rule1 , I am getting error message `You may not use unions,intersections,or array constants for Conditional Formatting criteria`. – Jill448 Dec 15 '14 at 15:25
  • changed sheet references to `!` - I had accidentally put a colon after the sheet name instead of the exclamation point – SeanC Dec 15 '14 at 19:09
  • `Rule 4` is not working. If I place the conditions individually they are working as needed. But they are not working when I am combining them with `AND` operator. I have updated my question as well. All are working now, except `Rule 4` – Jill448 Dec 15 '14 at 19:18
  • I copied the formulas to a new sheet, and tested, and got everything I expected. if rule 1 and 2 are working, then copy those. you can set up a formula to copy ready for the paste into conditional formatting. put `=(AND(1,2))` into a cell, copy formula 1 over the number 1, and formula 2 over the number 2, and copy that whole formula into a new conditional format. – SeanC Dec 15 '14 at 21:32
  • I did the same, but not sure why its not working.I tried changing the order of the rules as well. But its affecting Rule 1. – Jill448 Dec 15 '14 at 21:45
  • rule 4 will only affect rule 1 if it matches rule 2 at the same time. To test, you can move rule 4 to the top, and you will find that nothing gets highlighted in green, as the other 2 rules will override the highlighting – SeanC Dec 15 '14 at 22:03
0

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.

pnuts
  • 58,317
  • 11
  • 87
  • 139
  • Your understanding is correct.I need one more rule to the above mentioned one. The cell that satisfies both the 2 rules with different color(say green). But I am not clear why you mentioned conditional formatting will not allow these rules? I can have separate rules right? – Jill448 Dec 12 '14 at 18:57
  • I want the Rule 1 to be on complete Sheet2, hence made it as `=NOT(ISERROR(MATCH($1:$1048576,Sheet1!$D:$D,0)))` for blue font. But its still not working – Jill448 Dec 12 '14 at 19:13