Google sheets - conditional formatting from other sheet

Question

I have two spreadsheets (S1 and S2). I want to apply conditional formatting to column D from S1 such that:

• if text from cell D1, D2,... from S1 is present in column A from S2, a cell is colored green
• if text from cell D1, D2,... from S1 is not present in column A from S2, a cell is colored red

Could not get my mind around it, could you please help? I assume I must use a formula, but not sure how...

Answer 1

Select S1!D:D and (i) apply 'standard' red fill, (ii) Format > Conditional formatting..., Format cells if... Custom formula is and:

=match(D1,indirect("S2!A:A"),)

with green highlighting and Done.