Forum Discussion
Jase-R
Feb 18, 2024Copper Contributor
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 she...
- Feb 18, 2024
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.
HansVogelaar
Feb 18, 2024MVP
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.
Jase-R
Feb 23, 2024Copper Contributor
This worked a charm with <> in lieu of << thanks for your help