Highlight Cell if the value does not appear in another worksheet

[Blayke]

I need it to highlight red for the below scenario.

Sheets 3 – ‘Column B’ and Sheet 4 – ‘Column A’ do not match a value in sheet 2 ‘Column J’

I am currently using this for it to flag green if sheet 2 matches in sheet 4
=NOT(ISERROR(VLOOKUP($J1:$J500,'sheet 4!$A$1:$A$427,1,FALSE)))

However, cannot get it work to highlight red on sheet 4 if the value doesn’t appear in sheet 2

[#2]

@Blayke 

Example: If the value is not found in Sheet1, range A1:A3 then in Sheet2 E1 Red.

=COUNTIF(Tabelle1!$A$1:$A$3,E1)=0

Paste the formula into conditional formatting

Example file included.

Hope I was able to help you with this info.

NikolinoDE

[#3]

@NikolinoDE 

So I've got it running, but it keeps highlighting blank cells red as well, what's the best way to fix it? - 

Also do you know how to have a running count of how many cells are red where the count will appear on sheet 1, example below?