Forum Discussion

Jase-R's avatar
Jase-R
Copper Contributor
Feb 18, 2024
Solved

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...
  • HansVogelaar's avatar
    Feb 18, 2024

    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.

Resources