Highlight if cell exists in a range in other sheet

Question

I'm trying to highlight a row (or cell, if that's easier) if that value exists in a range in an other sheet in the same document.

Details: In Google Sheets, I have one sheet named "Application", with email addresses in column D. In a different different sheet (named "Accepted") I have the same data copied from the application sheet, also in column D.

I want to highlight the cells with email addresses in the "Application" sheet which has been copied over to the "Accepted" sheet.

I know Conditional formatting with Custom formula is the way to go, but I'm unable to find the correct syntax.

What is the correct formula for this?

Answer 1

In Google Spreadsheets conditional formatting across sheets is not nearly as straightforward as within a single sheet, because of security and therefore authorisation. You may, for speed for example, prefer to copy the content of Accepted into Application to avoid that issue, or write a script instead. At least keep the ranges as small as practical.

However it is possible, though may be slow and require authorisation.

Please clear any conditional formatting from Application ColumnD then:

Select ColumnD in Application, Format, Conditional formatting..., Format cells if... Custom formula is and

=COUNTIFS(IMPORTRANGE("k e y","Accepted!D:D"),D1)>0

with highlighting of your choice and Done.

k e y above represents the unique identification code for Accepted (will look something like 1u4vq8vDne-aKMVdJQPREGOxx7n99FqIb_kuJ_bG-PzM).