Automatically highlight a cell in the column of one sheet if it doesn't appear in other sheets

[Jase-R]

I am wondering if anyone can help me

I have customer names with supply requests that I copy from a daily report. I have two other sheets that reference the names exactly to automatically fill sheets for printing.

The issue is if we have a new customer coming in I would like that cell to highlight to show that the name doesn't exist in the other sheets

ie. Sheet 1 has unqiue customer names in column d, If that name does not appear in any cells in sheet 2 and sheet 3 I would like it to automatically fill the cell yellow.

Any help would be greatly appreciated 🙂

Jason

[#2 · Accepted solution]

@Jase-R 

Assuming that D1 contains a header, select D2:D100 or however far down you want.

D2 should be the active cell in the selection.

On the Home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula

=AND(D2<<"", COUNTIF('Sheet2'!A:ZZ, D2)+COUNTIF('Sheet3'!A:ZZ, D2)=0)

Replace Sheet2 and Sheet3 with the real names of those sheets.

Click Format...
Activate the Fill tab.
Select a highlight color.
Click OK, then click OK again.

[#3]

Thanks for the quick reply, unfortunately it comes up with a broken formula error.

Anything I could be doing wrong?

[#4]

The part I didn't recognise was
D2<<""
Replacing it by
D2<>""
got things working for me.

[#5]

@Jase-R 

Sorry about that, << should have been <> as @PeterBartholomew1 pointed out

[#6]

This worked a charm with <> in lieu of << thanks for your help