Forum Discussion
Automatically highlight a cell in the column of one sheet if it doesn't appear in other sheets
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
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.
5 Replies
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.- This worked a charm with <> in lieu of << thanks for your help
- Thanks for the quick reply, unfortunately it comes up with a broken formula error.
Anything I could be doing wrong?
