Forum Discussion

Catherine_Robertshawe's avatar
Catherine_Robertshawe
Copper Contributor
Aug 15, 2022

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

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)))

  • GingerNinjaneer's avatar
    GingerNinjaneer
    Copper Contributor

    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.

    • Catherine_Robertshawe's avatar
      Catherine_Robertshawe
      Copper Contributor
      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!
      • GingerNinjaneer's avatar
        GingerNinjaneer
        Copper Contributor

        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.

         

Resources