Conditional formatting in a column that highlights the cell if it matches data in another worksheet

[Catherine_Robertshawe]

Hello!

I am wanting to apply a conditional format formula that will highlight a cell in a colmun if it contains the same data from a column in another sheet. The formula I have found is not working and just highlights the entire colmun when I apply it. The formula I have found is the following formula: =NOT(ISERROR(VLOOKUP($B3,'Sheet2'!A:A,1,FALSE)))

[#2]

@Catherine_Robertshawe try using this in the conditional formatting menu.

=Countif('sheet 1'!$C$2:$C$5,$B2)=0

That seemed to work for me. You will need to put a second rule in so that it doesn't highlight the blanks though.

[#3]

Thank you! Sorry I realise that I copied the original formula I was trying to use incorrectly, as I am trying to match the data from a separate sheet in the same workbook. I have updated my post, but the formula that I was trying that is not working and just highlighting the entire column instead of the matched cells is: =NOT(ISERROR(VLOOKUP($B3,'Sheet2'!A:A,1,FALSE))). I tried your formula just replacing the range with the same range as above ('Sheet2!A:A) and it also just highlighted the entire column rather than the relevant matching cells. Thanks for your help though!

[#4]

@Catherine_Robertshawe I just realised that the formula that I gave you will highlight everything that isn't the same. If you replace the =0 with =1 it should work.

This is what I've got in mine to highlight any cells that don't match the column/range on the other sheet.