Conditional Formatting

[Opgelost]

Dear superusers,

I would like to use conditional formatting with a formula to do the following:

Color all cells red in SHEET 1, COLUMN A if it contains data which is NOT included in SHEET 2, COLUMN A.

Thanks very much!

Opgelost
(The Netherlands)

 

[mattmickle]

In Sheet 1, Column B, use a MATCH formula : =MATCH(A1,Sheet2!$A$1:$a$10,0) - adjust range to match your data
copy this formula next to all cells with data on Sheet 1.
in your conditional formatting, create a New rule, use a formula to determine which cells to format.
your formula in the conditional formatting box will be =ISERROR($B1)
Format Red
Copy this formatting to all applicable cells.

 

[Eric W]

Another option:

Select Sheet1, column A. Click Conditional Formatting > New Rule > Use a formula > and enter:

=AND(A1<>"",COUNTIF(Sheet2!$A:$A,A1)=0)

Click Format... and choose your fill color.

 

[Opgelost]

another option:

Select sheet1, column a. Click conditional formatting > new rule > use a formula > and enter:

=and(a1<>"",countif(sheet2!$a:$a,a1)=0)

click format... And choose your fill color.

it works! Thanks!

 

[Eric W]

Glad we could help!